Solved: Dynamic Table - Part 3 adding TAU Value

Anonymous · ‎08-09-2019

Hi Experts

1 m referring to the folloiwing Kendall's Tau Quick Measure here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Kendall-s-Tau/m-p/625107.

This is a calculation that ws created by a Super User on this forum by Greg Deckler.

I am trying to re create the same calculation using my dataset. Sample file attached. I am trying to get the TAU Gregs link using data from the link below. The values i would like to use from my data set are Material_id and FISCAL_MON_START_DT as the two variables. Which find these in the main FACT table PMS_Complaints.

Please refer to tab Post Market Surveillance Trending

https://www.dropbox.com/s/i0nffe5gso2d684/Sample__.pbix?dl=0

This is the last measuer i am trying to work out.

below is the real world example of this calculation see value I8 the tau.

Anonymous · ‎08-15-2019

New table =
FILTER (
    ADDCOLUMNS (
        VALUES ( PMS_COMPLAINT[FISCAL_MON_START_DT] ),
        "CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),
        "__Index", RANKX ( PMS_COMPLAINT, PMS_COMPLAINT[FISCAL_MON_START_DT],,, DENSE )
    ),
    [FISCAL_MON_START_DT] <> BLANK ()
)

View solution in original post

parry2k · ‎08-10-2019

@Anonymous link to Quick Measure is not working, can you repost to check what is it and how it can be used in your model.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎08-10-2019

Hi Perry2k

The answer lie with the greg decker link on how you can implement the measure....he uses two variables and sorts the data interviewer 1 and 2. I want to implement the same principles with my data using variables as I mentioned in above post...

I have created a table called table in the new link (new file uploaded), i am trying to create measures for Concordant and Discordant as greg did and then work out the Tau.

See Table in new link below

My Index column is my FISCAL_MON_START_DT

and My interviewer 2 is my Material_ID

So far i have (which is not correct)

Tau = 
VAR Mytable = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)))
VAR __table1 = ADDCOLUMNS(Mytable,"__Concordant",COUNTROWS(FILTER(Mytable,[FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]>EARLIER([CountComplaints]))))
VAR __table2 = ADDCOLUMNS(__table1,"__Discordant",COUNTROWS(FILTER(Mytable,[FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]<EARLIER([CountComplaints]))))

VAR __C = SUMX(__table1,[__Concordant])
VAR __D = SUMX(__table2,[__Discordant])

RETURN

ABS(DIVIDE(__C - __D , __C + __D,0))

It's not a quick measures answer.

see new file https://www.dropbox.com/s/jizl49iyw6pkg2p/Sample__%281%29.pbix?dl=0

Anonymous · ‎08-15-2019

New table =
FILTER (
    ADDCOLUMNS (
        VALUES ( PMS_COMPLAINT[FISCAL_MON_START_DT] ),
        "CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),
        "__Index", RANKX ( PMS_COMPLAINT, PMS_COMPLAINT[FISCAL_MON_START_DT],,, DENSE )
    ),
    [FISCAL_MON_START_DT] <> BLANK ()
)