Need help

[yousaf]

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 is, not all equipment are tested every month and when i plot, i just get some number of tests done on equipment in a month. I need record against all equipment every month corresponding to the rating. at that time. Any leads? 

 DataGraph

need something like this

TIA!

[Ashish_Mathur]

Hi,

You may refer to my solution here - Determine latest condition of each equipment and show a month wise count.

Hope this helps.

[Jihwan_Kim]

Hi, @yousaf 

Please kindly have a look at the below picture.

I am not sure what happened in your pbix file, but I fixed the Equipments-Table and created a new one to match with the new data.

please also check the link below.

And, please kindly let me know if it works for you, otherwise, I will try to find a different way.

https://www.dropbox.com/s/xmpl5mpoho68fzo/yousaf%202021%200320.pbix?dl=0 

If this helps, Appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[yousaf]

Currently testing it. I'll let you know soon. Thanks

[Jihwan_Kim · Accepted solution]

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 )

)

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

[yousaf]

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

[Jihwan_Kim]

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]

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

[Jihwan_Kim]

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.