Fixed function Tableau to Power BI DAX help!

[jharris32]

I need to convert this fixed LOD in tableau to DAX, can anyone help?

{ FIXED [Matter/Case ID# (GAL Permanency Goals)]:COUNT([Permanency Goal])}

[v-yangliu-msft · Accepted solution]

Hi  @jharris32 ,

Here are the steps you can follow：

1. Create measure.

Average Permanency Goals Per Case =
AVERAGEX(
    FILTER(ALL('Table'),
    'Table'[Close Reason]=MAX('Table'[Close Reason])),[Total Permanency Goals (the measure from above)])

2. Select [Measure] – Measure tools – Set the decimal point

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yangliu-msft]

Hi  @jharris32 ,

I created some data:

Are you referring to the de-duplication counts for [Permanency Goal] grouped by [Matter/Case ID] in the table?

Here are the steps you can follow：

1. Create measure.

Measure =
CALCULATE(
    DISTINCTCOUNT(
        'Table'[Permanency Goal]),
        FILTER(ALL('Table'),
        'Table'[Matter/Case ID]=MAX('Table'[Matter/Case ID])))

2. Result:

If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jharris32]

This is exactly what I'm looking for, it worked perfectly - thank you! 

Part 2 to this one - If I want to then calculate the average number of permanency goals per case based on the close reason, is that possible? 

Below is what I'm trying to get to. So the top chart would be what I have with the formula you provided and then the bottom (in red) is what I need a DAX formula for.  

Matter/Case ID	Total Permanency Goals (the measure from above)	Close Reason
x	2	AD
y	3	GU
z	2	REU
a	1	REU
b	4	GU
c	1	AD
Close Reason	Average Permanency Goals Per Case
AD	1.5
GU	3.5
REU	1.5

THANKS!

[ryan_mayu]

could you pls provide some sample data and expected output?