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, 2022 in Power BI by surbhi
• 3,810 points
1,530 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
        )
    )

Unlock the Power of Data Visualization with Our Comprehensive Power BI Course!

answered Feb 23, 2022 by CoolCoder
• 4,400 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,910 points
3,642 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,050 points
7,838 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
788 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,030 points
152,432 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
990 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
2,447 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
1,126 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,360 points
1,417 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, 2022 in Power BI by CoolCoder
• 4,400 points
1,201 views
0 votes
1 answer

What is DAX in Power BI?

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

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