topic Re: DAX Expression to choose SSAS tabular model Measure based on Dimensional table selection. in DAX Commands and Tips

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

Anonymous — Tue, 22 Nov 2022 21:07:48 GMT

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

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

v-jianboli-msft — Wed, 23 Nov 2022 07:13:54 GMT

Hi Anonymous ,

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.

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

Anonymous — Wed, 23 Nov 2022 16:24:58 GMT

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.

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

Anonymous — Mon, 09 Jan 2023 17:31:18 GMT

We ended up removing Offset count from the cube.