Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

First Date where the % is less than 90

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?    
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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's avatar
    Anonymous
    4 years ago

    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:

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Also the Count and Slots are in another different tables.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      4 years ago

      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 )