dimanche 16 mai 2021

DAX How to count/summarise the result of a measure

I am new to Dax and spent the better part of 24 hours trying to get 2 formulas working. I believe I have the first working and trying to figure out the second formula.

I am trying to write Dax to compare 2 date columns in a table to determine if an incident was resolved on the same day or not.

number opened date resolved date Calculated measure
1nc01 01/05/2017 01/05/2017 True
inc02 01/05/2017 02/05/2017 False
inc03 02/05/2017 02/05/2017 True
inc04 01/05/2017 03/05/2017 False
inc05 03/05/2017 03/05/2017 True

The calculated measure in the above table is not part of the Power BI table, it is included just for illustration.

Code 1 below is what I use to generate is.

Code 1

Resolved Same Day = 
   IF(
       SUM(Incident[Opened Date]) = SUM(Incident[Resolved Date]), "True", "False"

)

Code 2 - I tried a variation of the below for my second query (count the number of true and false in the measure)

Resolved Same Day = 
COUNTROWS(
    FILTER(Incident, incident[Opened Date] = Incident[Closed Date]))

Total Resolved Same Day (True) = COUNTX(FILTER(KeyMeasures,KeyMeasures[Resolved Same Day]="True"))
Total Resolved Same Day (True) = COUNTROWS(
    FILTER(KeyMeasures,KeyMeasures[Resolved Same Day]="true")
Measure = 
    FILTER(KeyMeasures,KeyMeasures[Resolved Same Day]="true"))

My overall goal is to develop a measure to calculate the below: ([Number of incidents resolved on the same day opened]/[total Number of resolved incidents])*100

DAX may read something along the lines of [Measure(same day)/measure (total incidents)]*100

I will be grateful for any help on this.

Aucun commentaire:

Enregistrer un commentaire