Forum Discussion

yousaf's avatar
yousaf
Helper I
5 years ago
Solved

Need help

Hi Guys,, I am trying to replicate a web report in power bi. There is a graph which is shows the overall equipment rating based on tests done every month, rating being Legend here. Now the problem i...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi,

    I hope the below is what you are looking for.

     

    I created a measure that gives The LATEST Rating result of the equipment among the current or from before until the current month. I also considered if one Equipment has two Ratings on the same day, then give the maximum result of Ratings, because "selectedvalue(Data[Rating])" gives blank value if it has two results.

     

    test result (if it is not in this month, show last month result) =
    VAR lasttestdate =
    CALCULATE (
    MAX ( Data[Test_Date] ),
    FILTER ( ALL ( dates ), dates[Date] <= MAX ( dates[Date] ) )
    )
    VAR withresult =
    CALCULATE ( SELECTEDVALUE ( Data[Rating], MAX(Data[Rating]) ), dates[Date] = lasttestdate )
    RETURN
    withresult
     
    Then, I created the below measure.
     
    equipment count by test result =
    VAR newtable =
    SUMMARIZE (
    ALL(Equipments),
    Equipments[Equipment],
    "@testresult",
    [test result (if it is not in this month, show last month result)]
    )
    VAR filtertable =
    FILTER (
    newtable,
    [@testresult]
    = SELECTEDVALUE ( Ratings[Rating] )
    )
    RETURN
    IF (
    ISBLANK ( [count of equipment] ),
    BLANK (),
    COUNTROWS ( filtertable )
    )
     
     
yousaf's avatar
yousaf
Helper I
5 years ago

Can you please share your date table dax here? I'm unable to open your file as its newer version.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi,

Thank you for your message.

I thought you could open even the version is different. 🤔

 

I am adding links down below for the two files.

One is PBIX file (please try again), and the other is the date table csv file.

I usually create a date table by using M in Power Query Editor.

 

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

 

 

date table link

https://drive.google.com/file/d/18vThqVa49HhUvmBZohSc3xkDrxsZDLNB/view?usp=sharing 

 

 

pbix file link

https://drive.google.com/file/d/17f_Q-xaoBpOodSLfT1WKq6g9y5Vyy585/view?usp=sharing 

  • yousaf's avatar
    yousaf
    Helper I
    5 years ago

    Its still not really working for my data but anyways I'm really thankful and appreciate the help.🙂

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Oh,

    Perhaps, 

    try to assign the new-dates-table as a date table.

    In some cases, if you do not assign the created date table as a date table, date-related-measures would not work.

     

    I hope it helps.