Count rows per category in slicer

[rdnguyen]

Hi All,

I have the following relationship model and was trying to count how many Job per Workcenter. My calculation as below failed to bring up any number and my coffee didn't really help me much 😉

Kindly help me understand what I am missing here? Especially what if I want to count if the relationship is not established (inactive one)?

[rdnguyen]

I think I found the answer for myself. I created all relationship possible from Dim to Fac (1 active and 2 none), then apply this measure calculation:

m_HideEmpty =

CALCULATE(COUNTROWS('Job-WCRef_job'),USERELATIONSHIP('DIM-WorkCenter'[c_sitewcID],'Job-WCRef_job'[h_sitewcID-current]))

[johnt75 · Accepted solution]

If there is an inactive relationship then you could do something like

c_HideEmpty =
SUMX (
    'DIM-WorkCenter',
    COUNTROWS (
        CALCULATETABLE (
            RELATEDTABLE ( 'Job-WCRef_job' ),
            USERELATIONSHIP ( 'DIM-WorkCenter'[Column 1], 'Job-WCRef_job'[Column 2] )
        )
    )
)

If there is no relationship at all then you could use TREATAS, something like

c_HideEmpty =
SUMX (
    'DIM-WorkCenter',
    COUNTROWS (
        CALCULATETABLE (
            'Job-WCRef_job',
            TREATAS ( { 'DIM-WorkCenter'[Column 1] }, 'Job-WCRef_job'[Column 2] )
        )
    )
)

[rdnguyen]

@johnt75 what about if there is no relationship or inactive relationship? As I have 3 foreign keys in fact table mapping to the same Dimension?

[johnt75]

Try

c_HideEmpty = SUMX( 'DIM-WorkCenter', COUNTROWS(RELATEDTABLE('Job-WCRef_job')))

[rdnguyen]

This would count total, not every single category. Your later reply was absolutely useful. I found one for me, then notice your reply was far better. Kudo to you and thanks alot for your help!!!!