First Date where the % is less than 90

[Anonymous]

Hi,

I have below report, I want to show the first date where the % is less than 90% per State per RC.

Can anyone help?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

First date less than 90% =
VAR newtable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[State], Data[RC], Data[Date] ),
        "@ratio", CALCULATE ( DIVIDE ( SUM ( Data[Count] ), SUM ( Data[Slots] ) ) )
    )
VAR filtertable =
    FILTER ( newtable, [@ratio] < 0.9 )
RETURN
    IF ( HASONEVALUE ( Data[State] ), MINX ( filtertable, Data[Date] ) )

[Anonymous]

Hi @Jihwan_Kim ,

It is working now, thanks for your help.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

First date less than 90% =
VAR newtable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[State], Data[RC], Data[Date] ),
        "@ratio", CALCULATE ( DIVIDE ( SUM ( Data[Count] ), SUM ( Data[Slots] ) ) )
    )
VAR filtertable =
    FILTER ( newtable, [@ratio] < 0.9 )
RETURN
    IF ( HASONEVALUE ( Data[State] ), MINX ( filtertable, Data[Date] ) )

[Anonymous]

Hi @Jihwan_Kim ,

Just on last thing, Can you please help with how to get the Count and slots figure for the first date, at the moment I get the sum.

Thanks.

[Jihwan_Kim]

Hi,

Please check the attached file.

Thank you.

Count on First date less than 90% = 
VAR newtable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[State], Data[RC], Data[Date], Data[Slots], Data[Count] ),
        "@ratio", CALCULATE ( DIVIDE ( SUM ( Data[Count] ), SUM ( Data[Slots] ) ) )
    )
VAR filtertable =
    FILTER ( newtable, [@ratio] < 0.9 )
VAR mindate =
    MINX ( filtertable, Data[Date] )
VAR countfigure =
    SUMX ( FILTER ( newtable, Data[Date] = mindate ), Data[Count] )
RETURN
    IF ( HASONEVALUE ( Data[State] ), countfigure )

Slots on First date less than 90% = 
VAR newtable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[State], Data[RC], Data[Date], Data[Slots], Data[Count] ),
        "@ratio", CALCULATE ( DIVIDE ( SUM ( Data[Count] ), SUM ( Data[Slots] ) ) )
    )
VAR filtertable =
    FILTER ( newtable, [@ratio] < 0.9 )
VAR mindate =
    MINX ( filtertable, Data[Date] )
VAR slotsfigure =
    SUMX ( FILTER ( newtable, Data[Date] = mindate ), Data[Slots] )
RETURN
    IF ( HASONEVALUE ( Data[State] ), slotsfigure )

[Anonymous]

Hi @Jihwan_Kim ,

Thanks for your quick response.

I get error because the date is in Date table, State is in the Location table, they are not in the same table.

[Anonymous]

Also the Count and Slots are in another different tables.

[Jihwan_Kim]

HI,

Thank you for your feedback.

Sorry that I cannot imagine how your data model looks like.

I think, sharing your sample pbix file will help. 

[Anonymous]

I can't share the pbix because I have tabular model.

Attached is a simple draw of the model.

[Anonymous · Accepted solution]

This is my DAX:

First date less than 90% =
VAR newtable =
ADDCOLUMNS (
SUMMARIZE ( Count, Location[State], Location[RC], Data[Date] ),
"@ratio", CALCULATE ( DIVIDE ( SUM ( Count[Count] ), SUM ( Slots[Slots] ) ) )
)
VAR filtertable =
FILTER ( newtable, [@ratio] < 0.9 )
RETURN
IF ( HASONEVALUE ( Location[State] ), MINX ( filtertable, Data[Date] ) )

and I get below, it correctly filter <90, but not the min date per RC: