DAX measure for Table Visual that Returns True/False Based on Related table related to 3rd table.

[Txtcher]

I need a measure that returns a true or false in a table visual.

Fact Table:  HCR Intakes

Related Fact Table:  Allegations

Dimension Table:  ANE 

I am trying to build a table visual containing columns from HCR Intakes. HCR Intakes[IntakeID] has a one to many relationship with Allegations[IntakeID]. In the table visual, I want to add a column that returns a True/False if the HCR Intake has an ANE Allegation: Allegation[Allegation]=ANE[ANE Allegation].

[v-hashadapu]

Hi @Txtcher , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Shahid12523 · Accepted solution]

Has ANE Allegation =
IF(
CALCULATE(
COUNTROWS(
FILTER(
'Allegations',
'Allegations'[IntakeID] = SELECTEDVALUE('HCR Intakes'[IntakeID]) &&
'Allegations'[Allegation] IN VALUES('ANE'[ANE Allegation])
)
)
) > 0,
TRUE(),
FALSE()
)

[johnt75]

You could try a measure like

Has ANE Allegation =
IF ( NOT ISEMPTY ( CALCULATETABLE ( ANE, Allegations ) ), "Yes", "No" )

This works by using the Allegations table as a filter - the expanded Allegations table will include all columns from the ANE table, and the Allegations table is itself filtered by the Intake ID in your table visual.

[Sahir_Maharaj]

Hello @Txtcher,

Can you please try this approach:

Has ANE Allegation :=
VAR CurrentIntake = MAX ( 'HCR Intakes'[IntakeID] )
RETURN
IF (
    CALCULATE (
        COUNTROWS ( 'Allegations' ),
        KEEPFILTERS ( 'Allegations'[IntakeID] = CurrentIntake ),
        TREATAS ( VALUES ( 'ANE'[ANE Allegation] ), 'Allegations'[Allegation] )
    ) > 0,
    TRUE(),
    FALSE()
)

[Txtcher]

@Sahir_Maharaj 
Thank you for your response. I tried the measure, but unfortunately when I add it to the visual, I eventually receive an error stating the query has exceeded the available resources.

[Txtcher]

I think I figured it out. It took a few steps and I do not know if it is correct, but it worked:

1. Added a calculated column to the Allegations table:

Is ANE = 
    IF(
        RELATED('ANE'[ANE Allegation]) = Allegations[Allegation], 
        "Yes",
        "No"
    )

2. Added a calculated column to the HCR Intakes table:

ANE = 
If(
    COUNTROWS(
        FILTER(RELATEDTABLE(Allegations),Allegations[Is ANE] = "Yes"))
        > 0,
        "Yes",
        "No"
    )  

If anyone has a better more efficient solution, please share. Otherwise, my method appears to be working.