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

Question

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.&nbsp;&nbsp;FACT_A - TransactionsTRN_FACT_ID,&nbsp; &nbsp;POSTED_DATE,&nbsp; TRN_AMT1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10000&nbsp;FACT_B - Offset TransactionOFFSET_FACT_ID,&nbsp; TRN_FACT_ID,&nbsp; Posted Date, TNR OFFSET Amt234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20221001&nbsp; &nbsp; &nbsp; &nbsp; 3000345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2000456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10000&nbsp;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&nbsp; to bring in offset count and created a measure on it.&nbsp;&nbsp;Cube results(when only offset count is pulled with transaction)TRN_FACT_ID,&nbsp; &nbsp;POSTED_DATE,&nbsp; TRN_AMT,&nbsp; &nbsp;OFFSET COUNT1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;Cube results(If i pull in any attribute from Offset table)TRN_FACT_ID,&nbsp; &nbsp;POSTED_DATE,&nbsp; TRN_AMT,&nbsp; TNR OFFSET Amt, OFFSET COUNT1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;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,&nbsp; &nbsp;POSTED_DATE,&nbsp; TRN_AMT,&nbsp; TNR OFFSET Amt, OFFSET COUNT1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;&nbsp;

v-jianboli-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Please try:
OFFSET COUNT = COUNT('FACT_B - Offset Transaction'[TNR OFFSET Amt])
Final output:

Best Regards,
Jianbo Li
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

anonymous · Answer

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,&nbsp; &nbsp;POSTED_DATE,&nbsp; TRN_AMT,&nbsp; TNR OFFSET Amt, OFFSET COUNT1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;32&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20221001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp;I am getting count as total number of rows in Fact_B table. not as per my selection.&nbsp;&nbsp;

anonymous · Answer

We ended up removing Offset count from the cube.

Forum Discussion

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

3 Replies

Related content

Understanding Dimensional Modeling

Help!!! Expression Error

Regular expression

2 Dimensional Data

Expression Error