topic Re: retrieve latest non blank value if balnk value is selected in DAX Commands and Tips

retrieve latest non blank value if balnk value is selected

Sri57 — Mon, 24 Jun 2024 07:20:37 GMT

I have a table Test with the columns Name, Rating, and Year. I'm using a Year slicer. If I select the value 2023/24 from the slicer, then I should get the count of records from that year. If there is no data for 2023/24, it should check for the previous year, 2022/23. If there is also no data for 2022/23, it should check for 2021/22, and so on, until it finds data, and get the count of records for the selection of 2023/24.
I tried to implement the logic, but I failed to get it exactly right. For example, when I select 2023/24 and there is no data for it, we get no records. Instead, the measure needs to retrieve the count of records for 2022/23.
I have attached the sameple pbix file
https://www.dropbox.com/scl/fi/304a4rxdnmsb96ndgri5m/test1.pbix?rlkey=cv14d4zsqbsjp0m22y89h040o&st=ds96d08g&dl=0

TIA

Re: retrieve latest non blank value if balnk value is selected

aduguid — Mon, 24 Jun 2024 12:29:29 GMT

Give this one a try.

Count Records = VAR SelectedYear = MAX('Calendar'[Year]) VAR CheckYear = CALCULATE( MAX('Test'[Year]), FILTER( 'Test', 'Test'[Year] <= SelectedYear ) ) VAR ResultCount = CALCULATE( COUNTROWS('Test'), FILTER( 'Test', 'Test'[Year] = CheckYear ) ) RETURN ResultCount

Re: retrieve latest non blank value if balnk value is selected

Anonymous — Tue, 25 Jun 2024 09:57:50 GMT

Hi Sri57, aduguid,

Thank you for your prompt reply!

To meet your requirements, it’s necessary to use a name slicer to replace the name filter in table visual.

Then we need to add the Fiscal Year slicer from another table: Calendar to avoid blank value result.

Lastly, create a new measure in Audit table and drag the measure in visual filter to control how visual display latest non blank value:

Measure = var _slicerYear=SELECTEDVALUE('Calendar'[Fiscal Year]) var _FilterCount=COUNTROWS(FILTER(ALLSELECTED('Audit'),[FISCAL_YEAR]=_slicerYear)) var _beforeYear=MAXX(FILTER(ALLSELECTED('Audit'),[FISCAL_YEAR]<=_slicerYear),[FISCAL_YEAR]) RETURN SWITCH(TRUE(), ISFILTERED('Calendar'[Fiscal Year])=FALSE(),1, _FilterCount=BLANK() && MAX('Audit'[FISCAL_YEAR])=_beforeYear,1, _FilterCount <> BLANK() && MAX('Audit'[FISCAL_YEAR])= _slicerYear,1,0)

Result for your reference:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.