DAX Expression to choose SSAS tabular model Measure based on Dimensional table selection.

[hmed435]

I have desgined a tabular model cube in SSAS on transactionas. We have two facts one for transaction and one for offset transaction and there are multiple dimension tables. 

FACT_A - Transactions

TRN_FACT_ID,   POSTED_DATE,  TRN_AMT

1                      20221001         5000

2                      20221001         10000

FACT_B - Offset Transaction

OFFSET_FACT_ID,  TRN_FACT_ID,  Posted Date, TNR OFFSET Amt

234                        1                     20221001        3000

345                        1                     20221001         2000

456                         2                     20221001         10000

So we are not using FACT_B as a fact table in tabular model instead we are using it as a Dimensional table. But requirement is to get offset tran count as a measure. I did a count(*) and grouped tran_fact_id from FACT_B and left joined it to fact_a on TRN_FACT_ID  to bring in offset count and created a measure on it. 

Cube results(when only offset count is pulled with transaction)

TRN_FACT_ID,   POSTED_DATE,  TRN_AMT,   OFFSET COUNT

1                      20221001         5000                2

2                      20221001         10000              1

Cube results(If i pull in any attribute from Offset table)

TRN_FACT_ID,   POSTED_DATE,  TRN_AMT,  TNR OFFSET Amt, OFFSET COUNT

1                      20221001         5000                2000                 2

1                      20221001         5000                3000                 2

2                      20221001         10000              10000               1

Cube results(Expected results) (Offset count should be 1 on each line when an attribute is selected from offset table if not count should be 2)

TRN_FACT_ID,   POSTED_DATE,  TRN_AMT,  TNR OFFSET Amt, OFFSET COUNT

1                      20221001         5000                2000                 1

1                      20221001         5000                3000                 1

2                      20221001         10000              10000               1

[v-jianboli-msft]

Hi @hmed435 ,

Please try:

OFFSET COUNT = COUNT('FACT_B - Offset Transaction'[TNR OFFSET Amt])

Final output:

Best Regards,

Jianbo Li

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

[hmed435]

The Fact_B table is not used as a fact table in the cube tabular design instead it is used as a dimension table and added a relationship between Fact_A and Fact_B. I added count on Fact_B field but i am getting below results.

TRN_FACT_ID,   POSTED_DATE,  TRN_AMT,  TNR OFFSET Amt, OFFSET COUNT

1                      20221001         5000                2000                 3

1                      20221001         5000                3000                 3

2                      20221001         10000              10000               3

I am getting count as total number of rows in Fact_B table. not as per my selection. 

[hmed435]

We ended up removing Offset count from the cube.