How to add an additional filter on this DAX

[JustinDoh1]

I have uploaded my PBIX file here.

I am trying to add an additional filter based on State (like CO or NM).

Bottom is my measure I would like to modify:

Question =

IF( HASONEVALUE( vw_NH_ProviderInfo_CO[Facility]),

    VAR CurrentTestValue = [Forecast C2/3 Wtd]

    VAR _processingdate =

        Values ( 'Date Bridge'[ProcessingDate] )

    return

        CALCULATE(

            SELECTEDVALUE( Test_260303[HealthInspectionRating]),

            TOPN(1,

                FILTER(

                            ALL( Test_260303 ),

                            CurrentTestValue <= Test_260303[StarCutOff]

                            && Test_260303[ProcessingDate] IN _processingdate

                ),

                Test_260303[MaxTotalWHSS],

                ASC

            )

        )

)

Basically, current measure does not consider this coulumn:  [Test_260303].[Provider State].

I would like to appy [Test_260303].[Provider State] so that end result would be shown as 4 for ART and 3 for CAL:

ALL & ARB belong to CO state.

ART & CAL belong to NM state.

Somehow, it shows corretly for ALL and ARB on the "Combined" tab even with the wrong logic.

Thank you! 

[JustinDoh1]

@v-hjannapu Thank you so much for your help. I appreciate it greatly. I have another question coming up with very similar issue (adding a filter). I will try what I can do, but most likely will be posted within a day or so. Thanks.

[v-hjannapu]

Hi @JustinDoh1 

Thank you  for reaching out to the Microsoft fabric community forum and sharing the PBIX file and the measure.

Looking at the measure, the calculation is filtering the Test_260303 table only by StarCutOff and ProcessingDate. The Provider State column is not used anywhere in the filter. Because of this, when Power BI tries to find the matching HealthInspectionRating, it can sometimes take a row from a different state.

In your example, ALL and ARB are from CO and ART and CAL are from NM. Since the state is not part of the filter logic, the calculation may use rows from another state while deciding the rating. That can cause the value to appear incorrect for ART and CAL.

You may want to add the Provider State condition inside the FILTER part of the measure so that the calculation only checks rows for the same state. After adding that condition, please check if the results match the expected values.

Hope this helps. Let us know if the result changes after trying this.
Regards,
Community Support Team.

[JustinDoh1]

@v-hjannapu 

Thank you for your help. I was trying to modify like below, but I think it is missing something / logic. It does not make a change.

Question =

IF( HASONEVALUE( vw_NH_ProviderInfo_CO[Facility]),

    VAR CurrentTestValue = [Forecast C2/3 Wtd]

    VAR _processingdate =

        Values ( 'Date Bridge'[ProcessingDate] )

    var _State = Values ('Test_260303'[Provider State])    

    return

        CALCULATE(

            SELECTEDVALUE( Test_260303[HealthInspectionRating]),

            TOPN(1,

                FILTER(

                            ALL( Test_260303 ),

                            CurrentTestValue <= Test_260303[StarCutOff]

                            && Test_260303[ProcessingDate] IN _processingdate

                            && 'Test_260303'[Provider State] IN _State

                ),

                Test_260303[MaxTotalWHSS],

                ASC

            )

        )

)

[v-hjannapu]

Hi @JustinDoh1,

Thanks for trying the change and sharing the updated measure. The reason you are not seeing any difference is because ALL(Test_260303) removes the filters from that table, including the Provider State, so the calculation is still checking rows from all states.

I created a small sample PBIX and applied the state from the facility table inside the filter so it only checks rows for that state. I am attaching the PBIX here, please check once and see if this approach works in your model. Let us know how it goes.

Regards,
Community Support Team.

[JustinDoh1]

@v-hjannapu I am trying to understand how applying 'ALL' on table makes difference because when I tested without 'ALL', the outcome came out to be the same though. Thank you.

[JustinDoh1]

@v-hjannapu Thank you for your help. I am wondering
1) Why you have created two similar tables called "ProviderInfo" and "ProviderInfo (2)". Is there reason you have duplicated the table?

2) Why did you have to create another measure to get the SUM of Forecast C2/3 Wtd?

3) Why did you delete the 'IF( HASONEVALUE( vw_NH_ProviderInfo_CO[Facility])'? I do not see any difference with or without but just wondering.

Thanks.

[v-hjannapu · Accepted solution]

Hi @JustinDoh1,

Thanks for checking the PBIX and for your questions.
ProviderInfo and ProviderInfo (2)  there is no special reason for that. It happened while creating the sample model during testing. Only one ProviderInfo table is actually needed, so you can keep a single table in your model.
The SUM measure for Forecast C2/3 Wtd was created just to use it easily inside the main measure. It helps to keep the logic a bit cleaner, but if you already have a measure for that value, you can continue using the same one.
I removed the HASONEVALUE part in the sample just to keep the example simpler while testing the state filter logic. If your report always shows one facility at a time, keeping it or removing it will not change the result much. You can still keep that condition in your original measure if it is needed for your scenario.

The main idea in the sample PBIX was only to show how the Provider State can be applied inside the filter so that the rating is calculated based on the correct state rows.

Hope this clarifies it. Let us know if you have any other questions.

Regards,
Community Support Team.