Card Visual Returning Incorrect Value

[villa1980]

Hi all,

 I have a card visual that returns the latest date when the latest accident based on a DAX expression that filters on one of 2 groups

Ie.

Max_Date_By_Cat = CALCULATE(

    MAX(Workplace_Incidents[Date of Incident]), ALLEXCEPT(Workplace_Incidents,Workplace_Incidents[Platform])

    )

Last_Accident = DATEDIFF([Max_Date_By_Cat],TODAY(),DAY)+0

This works fine, however, I want it filter then on whether the incident was "Maj" and bring back the Last_Accident. But when I add that to the filter it returns the wrong value.

 As there have been no Incidents that are classed as "Maj"this month by either platform, the total should show show 0 but it is showing 1.

Any ideas

Thanks

Alex

[Anonymous · Accepted solution]

Hi @villa1980 ,

Please update the formula of measure [Max_Date_By_Cat] as below and check if it can return the expected result...

Max_Date_By_Cat = 
VAR _tab =
    SUMMARIZE (
        FILTER ( Workplace_Incidents, Workplace_Incidents[Incident] = "Maj" ),
        Workplace_Incidents[Platform],
        Workplace_Incidents[Date of Incident]
    )
RETURN
    MAXX ( _tab, [Date of Incident] )

If the above ones can't help you figure out, please provide some raw data in your table 'Workplace_Incidents' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous · Accepted solution]

Hi @villa1980 ,

Please update the formula of measure [Max_Date_By_Cat] as below and check if it can return the expected result...

Max_Date_By_Cat = 
VAR _tab =
    SUMMARIZE (
        FILTER ( Workplace_Incidents, Workplace_Incidents[Incident] = "Maj" ),
        Workplace_Incidents[Platform],
        Workplace_Incidents[Date of Incident]
    )
RETURN
    MAXX ( _tab, [Date of Incident] )

If the above ones can't help you figure out, please provide some raw data in your table 'Workplace_Incidents' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[villa1980 · Accepted solution]

Hi v-yiruan-msft,
What can I say, that has worked so well thank-you so much, took a while to understand what is going on with the expression but makes sense now....I should look at using VAR in the future when looking at these kind of expressions.

Many Thanks Again

Alex