How to get answer for question, How many Tags per resource I have?

[jaryszek]

Hello,

My model is:

nad now i want to get answer for a question: How many Tags per resource I have?

Tried to use measure:

New_Resources Count = 
DISTINCTCOUNT ( 'Dim_EA_Resource_Bridge_Tags'[DateResourceIdKey] )

But I got a constant...why? 

EDIT:

I created sample model like here:

used a measure :

Products per Tag = 
DISTINCTCOUNT( Bridge_ProductTags[ProductKey] )

and eveyrthing worked...oh but why?

Best,
Jacek

[amitchandak]

@jaryszek , measure should not be created be Dim level. It has created at fact level  

New_Resources Count = 
Countrows( Summarize(Fact,  'Dim_EA_Resource_Bridge_Tags'[DateResourceIdKey] ) )

. Make sure you have bi-direction join between the resource and Amortized Cost 

[jaryszek]

And futhermore,

I am trying to use best practices design from this topic:

https://community.fabric.microsoft.com/t5/Desktop/How-to-get-costs-for-Specific-Group-within-Bridge-...

to not use bidrectional relationships everywhere.

Best,
Jacek

[v-hjannapu · Accepted solution]

Hi @jaryszek,

Thank you  for reaching out to the Microsoft fabric community forum.

The main reason you are getting a constant value is that the filter from the Tag table is not reaching the bridge table in your actual model. Because of this, Power BI is counting all resources every time, instead of only the resources for the selected tag. That’s why your SQL query shows different counts, but the DAX measure gives the same number.

In your sample Product Tag model it works because the relationships allow the filter to flow correctly. But in your real model the filter direction is blocking it.

You can fix this by Turning on bidirectional filter on the relationship used for this calculation or Using a DAX measure that forces the filter to pass through.

After this, the DISTINCTCOUNT will respond correctly for each tag.

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Coummunity support Team.

[jaryszek]

Thanks,

measure should not be created be Dim level.

Why ?

I did this like that:

New_Resources Count 3 =
Countrows( Summarize(Fct_EA_AmortizedCosts, 'Dim_EA_AmortizedCosts_Resources'[DateResourceIdKey]) )

and still has constant:

And secondly, I tried with dbeaver anf t-sql on fanric with this query:

SELECT
tk.[Key],
tk.[Value],
COUNT(DISTINCT bt.DateResourceIdKey) AS ResourceCount
FROM Mart7.Dim_EA_Resource_TagKeys AS tk
JOIN Mart7.Dim_EA_Resource_Bridge_Tags AS bt
ON tk.DateKeyKey = bt.DateKeyKey
WHERE tk.[Key] = '__AzureDevOpsElasticPool'
GROUP BY
tk.[Key],
tk.[Value]
ORDER BY
COUNT(DISTINCT bt.DateResourceIdKey),
tk.[Key],
tk.[Value];

an it worked, showing different numbers not a constant ones. 
Why?

Best,
Jacek