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,820 points
1,875 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,420 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,770 points
4,230 views
0 votes
0 answers

What’s the best way to create dynamic date range filters in Power BI without writing complex DAX code?

What’s the best way to create dynamic ...READ MORE

Nov 6 in Power BI by Evanjalin
• 6,710 points
106 views
0 votes
1 answer
+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,060 points
8,345 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
1,334 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,735 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,520 points
1,509 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
1,657 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,420 points
1,696 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,420 points
779 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