Power BI DAX - adding date filter to DAX Query

0 votes

I am trying to add a date condition to the DAX query below, and I am struggling to find something to work. I basically want to filter on the Combination.Expiration Date column. For example, I dont want the query to return anything if there is no expiration date, or the expiration date is in the past. Below is the current DAX query.

List of Requirement values = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Combination'[Requirement])
VAR __MAX_VALUES_TO_SHOW = 90
RETURN
    IF(
        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
        CONCATENATE(
            CONCATENATEX(
                TOPN(
                    __MAX_VALUES_TO_SHOW,
                    VALUES('Combination'[Requirement]),
                    'Combination'[Requirement],
                    ASC
                ),
                'Combination'[Requirement],
                ", ",
                'Combination'[Requirement],
                ASC
            ),
            ", etc."
        ),
        CONCATENATEX(
            VALUES('Combination'[Requirement]),
            'Combination'[Requirement],
            ", ",
            'Combination'[Requirement],
            ASC
        )
    )

Can somebody assist me in adding a date filter to this?

Feb 23 in Power BI by surbhi
• 3,810 points
55 views

1 answer to this question.

0 votes

VALUES('Combination'[Requirement]) gives you a table with all the Requirement values in the Combination table.

If you want to exclude certain result from that table, you need to apply a filter to that table.

You have two options:

Option 1:
VAR tbl_values = CALCULATETABLE(VALUES('Combination'[Requirement]),
                'Combination'[Expiration Date] = BLANK() ||
                'Combination'[Expiration Date] < TODAY())

Option 2:
VAR tbl_filtered = FILTER('Combination', 
                'Combination'[Expiration Date] = BLANK() ||
                'Combination'[Expiration Date] < TODAY())

Then you can replace the VALUES in your measure

List of Requirement values = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Combination'[Requirement])
VAR __MAX_VALUES_TO_SHOW = 90
VAR tbl_filtered = FILTER('Combination', 
                'Combination'[Expiration Date] = BLANK() ||
                'Combination'[Expiration Date] < TODAY())

RETURN
    IF(
        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
        CONCATENATE(
            CONCATENATEX(
                TOPN(
                    __MAX_VALUES_TO_SHOW,
                    tbl_filtered,
                    'Combination'[Requirement],
                    ASC
                ),
                'Combination'[Requirement],
                ", ",
                'Combination'[Requirement],
                ASC
            ),
            ", etc."
        ),
        CONCATENATEX(
            tbl_filtered,
            'Combination'[Requirement],
            ", ",
            'Combination'[Requirement],
            ASC
        )
    )
answered Feb 23 by CoolCoder
• 4,360 points

Related Questions In Power BI

0 votes
1 answer

How to convert eight digit yyyymmdd to date using DAX in Power BI ?

You can go through this: column = IFERROR( ...READ MORE

answered Dec 8, 2020 in Power BI by Gitika
• 65,850 points
1,672 views
+1 vote
2 answers

Passing parameters to Power BI filter programmatically

In addition to @Kalgi's answer, there's another ...READ MORE

answered Sep 19, 2018 in Power BI by Nilesh
• 7,040 points
6,327 views
0 votes
1 answer

Dax query in power bi

Here is my table before I have ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,570 points
403 views
+2 votes
3 answers

How to replace null values with custom values in Power BI(power query editor)?

Hi Nithin, To fill or replace the null or ...READ MORE

answered Mar 8, 2019 in Power BI by Cherukuri
• 33,010 points
97,190 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,130 points
560 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 22, 2018 in Power BI by nirvana
• 3,130 points
1,829 views
+2 votes
2 answers

Migrate power bi collection to power bi embedded

I agree with Kalgi, this method is ...READ MORE

answered Oct 11, 2018 in Power BI by Hannah
• 18,570 points
630 views
+1 vote
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 52,350 points
728 views
0 votes
1 answer

Query from Power BI to AI suddenly fails with (502): Bad Gateway

The AA Query frequently returns too much ...READ MORE

answered Feb 10 in Power BI by CoolCoder
• 4,360 points
181 views
0 votes
1 answer

What is DAX in Power BI?

DAX is a programming language used in ...READ MORE

answered Feb 4 in Power BI by CoolCoder
• 4,360 points
38 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP