distinctcount with a related filter function

[Krijgersss]

Hello i need some help 

i want to make a formule for distinctcount with a related function because i need to filter my distinctcount from a related table (see screenshot)

[v-yiruan-msft]

@QuentinBl & @tamerj1  Thanks for your contribution on this thread.

Hi @Krijgersss ,

@QuentinBl  and @tamerj1 have provided you some suggestions, but it seems that can't help you get the expected result. Could you please provide some fake data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[tamerj1]

Hi @Krijgersss 

you can

Public Holidays =
SUMX (
    Dim_Emplyee,
    CALCULATE (
        COUNTROWS ( Public_Holidays ),
        USERELATIONSHIP ( Public_Holidays[Country], Dim_Employee[CountryCode] )
    )
)

[Krijgersss]

i tried this but this gives me the same result it doesnt let the filter function work. it's the way the model is build i need to count from a dimension table and i need to filter from a fact tabel i tried using crossfilter but this gives me extreem loeding times and wrong values

[tamerj1]

@Krijgersss 

Please ignore my above solution. I have edited it by mistake and pasted a solution that was intended for another question in the forum. 

I remember my original solution included summarizing the fact table by the dimension column. That should work unless there are more information that need to be provided. If you have an active one to many single direction relationship between the two tables, normally this should work. 

[QuentinBl]

Hello @Krijgersss , 

The easiest way is maybe to modify your relationship to allow filtering in both direction ? 
becareful, it can alter your model functionnality or performances. 

If only your disctinctcount need this filter, you can use a DAX Crossfilter function to modify your relationship in a specific measure.

[Krijgersss]

i tried using crossfilter but this gives me extreem loeding times and wrong values is also tried using it in my measure but that also doesnt work. it's because of the model is build i need to distinctcountnoblank on a dimension table and a filter from a fact table