Numerator calculations

[karkar]

Hello ,

I have data like this....many rows per ID based on the Med_Taken_time. Otherwise the other rows are same for an ID

I want to calculate Numerator and Denominator.

Numerator: Count of ID's in the 1,2, or 3 categories with Med_taken_time 12 hours from admission

Denominator: Count of ID's in all the categories 1,2, 3, -1, 0) ---we should consider only once

ID       Category          Admit                                Discharge                          Med_taken_time

001         1                   10thDEC10:00AM             12thDEC10:00AM                   11DEC9AM         

001         1                   10thDEC10:00AM             12thDEC10:00AM                   11DEC12AM              

001         1                    10thDEC10:00AM             12thDEC10:00AM                  11thDEC03pM               

001         1                    10thDEC10:00AM             12thDEC10:00AM                  11thDEC05pM         

001          1                   10thDEC10:00AM             12thDEC10:00AM                  12thDEC05AM 

002         -1                   11thDEC10:00AM             12thDEC10:00AM                   11DEC11AM         

002         -1                   11thDEC10:00AM             12thDEC10:00AM                   11DEC12AM              

002         -1                    11thDEC10:00AM             12thDEC10:00AM                  12thDEC10AM               

002         -1                    11thDEC10:00AM             12thDEC10:00AM                

002          -1                   11thDEC10:00AM             12thDEC10:00AM 

Thanks               

[Phil_Seamark]

Hi there,

Not sure if I have fully understod but perhaps see how this goes.

Please add a column to your table

MyColumn = IF( DATEDIFF(
                    MedTable[Med_taken_time],
                    'MedTable'[Discharge],
                    HOUR)>12,1,0)

Then you can add the following Measures

Numerator = CALCULATE(SUM(MedTable[MyColumn]))


Denominator = CALCULATE(DISTINCTCOUNT(MedTable[ID]))

If the measures are correct, they can be combined into a single measure using the DIVIDE function.

[karkar]

Thanks again Phil,

If I may ask what is the reason for using CALCULATE in the numerator and denominator calculations?

Wouldnt SUM or DISTINTCOUNT by itself give us the result?without needing to use CALCULATE !

Thanks

[v-huizhn-msft]

Hi @karkar,

You're right. If you want to add filter in the formula, you will use CALCULATE function. 

In addition, do you resolve your issue? If it does, please share your solution or mark the right reply as answer, which will help more people. 

Best Regards,
Angelia

[karkar]

Hello,

Thanks for your comment. But what is the filter in the following? MAX is the filter?

Numerator = CALCULATE(MAX(MedTable[MyColumn]))

Cant we just use the following instead of the above?:

Numerator = MAX(MedTable[MyColumn])

Thanks

[parry2k]

There is no filter in this CALCULATE function, as @v-huizhn-msft suggested, IF you need to add filter to your calculation then you will use CALCULATE function, in this case it is not required and no filter is used.

[karkar]

Hello Phil,

Thanks for taking time to solve the probelm. it looks like its doing the numerator calculation a different way.

[Phil_Seamark]

Perhaps try this?

Numerator = CALCULATE(MAX(MedTable[MyColumn]))