Need help with DAX

[Anonymous]

Hi Everyone, 

I am new to power bi and I am struggling with implementation of a card visual. 

The data model structure:
I have all_orgs table that has information for all the orgs present. Let's say org_id and org_member(yes or no field). Let's say I have 5k+ records in this table. 

I have another table participants which has 375 records only. it contains only [org_id]. Note that not all 375 orgs is present in the all_orgs table. Only 317 of them is present. 

I have an active one to one relationship between all_orgs table and the partcipants table with filter direction set to both. 

The objective:
I want to show a card visual which shows the count. ie out of 375 participants how many are present in all_orgs table. This I am able to achieve using the following DAX. There are 300 records present in all orgs table. 

CountPresentOrgs =

CALCULATE(

    COUNTROWS('participants'),

    FILTER(

        'participants',

        NOT(

            ISBLANK(

                LOOKUPVALUE(

                    all_orgs[ORG_ID],

                    all_orgs[ORG_ID], 'participants'[ORG ID]

                )

            )

        )

    )

)

however, when I apply the filter is_member on the all_orgs table:
1. the number of records in all_orgs table get reduced to 1k records. 
2. I know that only 200+ participants is present in this filtered table. 
3. the dax should show this 200 count but instead shows the original 300. 
4. Is there a way to get the present count based on the filtered all_orgs table. ie, out of the constant 375, I should get present and not present on the all_orgs_table which might be filtered. 

Any help is appreciated. Thanks!

[Anonymous]

Hi All,
Firstly sergej_og thank you for your solution!
And @Anonymous ,I tried to replicate your data for testing, when using is_member for testing, it is possible to filter according to the filter, it is possible that my data is too simple to replicate your specific problem, we also tried other DAX to realize your needs, you can try to see if it can accomplish your needs!

CountPresentOrgsFiltered = 
CALCULATE(
    COUNTROWS('participants'),
    FILTER(
        'participants',
        NOT(
            ISBLANK(
                RELATED('all_orgs'[ORG_ID])
            )
        )
    )
)

CountPresentOrgsFiltered2 = 
CALCULATE(
    COUNTROWS('participants'),
    TREATAS(
        VALUES(all_orgs[ORG_ID]),
        'participants'[ORG_ID]
    )
)

I hope my thoughts are helpful, and I would be honored if my approach solves your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[sergej_og]

Hey @Anonymous ,
can you pls provide a screenshot of your model...for better undestanding how fields are connected.
Pls mask sensitive data before sharing.

Thx.

Regards