topic Re: Suitable DAX for model having two fact tables and multiple dim tables in DAX Commands and Tips

Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Sun, 17 Mar 2024 09:22:31 GMT

Hi,
Requesting help on suitable DAX for creating matrix visual as below using two fact tables.
FactTable1 having ReasonCode ingested at various time windows for several devices
FactTable2 having Value ingested at various time stamps for several meters

DimTable2 has the description for Reasons
There is a bridge table DimTable1 mapping deviceID and meterID and has the description of machine name as well.
Also there are two Dim tables one for Date (DimTable3) and another for time (DimTable4).

Data model proposed as below.(PBIX file also enclosed)

Required basic matrix visual giving aggregated total of values (FactTable2) output sliced based on date window and machine name as below:

I had used calculated columns and got the output but since data size is huge it takes processing time and gets hanged.
I need a suitable DAX which will aggregate based on relation ship only for my requirement.

Re: Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Sun, 17 Mar 2024 09:21:36 GMT

Apologies for adding incorrect google drive link.
Please find the correct link as below:
https://drive.google.com/file/d/1ucuL0FoV5KIGT0vFSvybg4ORxW6-CPSs/view?usp=drive_link

Re: Suitable DAX for model having two fact tables and multiple dim tables

Anonymous — Mon, 18 Mar 2024 09:09:51 GMT

Hi, InsHunter

Unfortunately, the link you shared requires a login to personal Google account to open it, and due to privacy requirements, you need to provide a link that does not require a login to Google account to open the file.

Best Regards,
Yang
Community Support Team

Re: Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Sat, 23 Mar 2024 04:47:05 GMT

My sincere apologies for the same:
https://drive.google.com/file/d/1bVmbOCBNamuj7mX8ya-wCuFI7Aa6VHMJ/view?usp=sharing

Please confirm in order.

Re: Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Wed, 20 Mar 2024 08:10:36 GMT

lbendlin
Hi. In the past you had helped me for solutions. Can u please help with building DAX Measures for my current model.
Thanks...

Re: Suitable DAX for model having two fact tables and multiple dim tables

lbendlin — Wed, 20 Mar 2024 23:44:12 GMT

Your current model can be improved. You can wire in the Dates and Times tables, and I would recommend you separate the Devices from the Meters.

Re: Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Thu, 21 Mar 2024 09:39:53 GMT

lbendlin

Hi Many thanks for your response. Iam sorry I could not get your suggestions. I dont know how to use the time combined with the date as a slicer for the visual filter and hence using only the date for the slicer. Currently using only calculated column for the "event" table as below. But its taking lot of time due to size in the original data set.The DAX for calculated column "Value_mapped" as in original data set (events table)

is as below:

Value_mapped = VAR Energy_table_from_meter = CALCULATETABLE ( meter, CROSSFILTER ( meter[MeterID], DeviceID_MeterID_Mapping[MeterID], BOTH )) VAR filtered_Energy_table_from_meter = FILTER ( Energy_table_from_meter, meter[Date_Time] < events[EndDatetime] && meter[Date_Time] >= events[StartDatetime] ) RETURN SUMX(filtered_Energy_table_from_meter, meter[value])

Iam using this to summarise by a DAX (SUMX)on the calculated column field.But value being arrived at also slightly differs from the output of the person giving summary in SQL using "between" function.

Request your help for DAX directly on to matrix visual with out going for calculated column. Iam a newbie to POWER BI.
Your help as in the past is deeply appreciated.

Thank you.

I would request the DAX for the measure.

Re: Suitable DAX for model having two fact tables and multiple dim tables

lbendlin — Fri, 22 Mar 2024 01:14:27 GMT

You can only start working on the measure after you get your data model in order. Have you made the changes I suggested?

Re: Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Fri, 22 Mar 2024 10:09:06 GMT

Hi,
My apoogies Iam unable to get your point on the wiring my time dimtable to the time fields of my factable..Since iam using only date slicer for visual do i need to use the time. Also the DAX I suppose will use the Date time field directly for the window capture of value.
Requesting you to elaborate on the same.

Thank you.

Re: Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Fri, 22 Mar 2024 10:09:52 GMT

Typo-"apologies"

Re: Suitable DAX for model having two fact tables and multiple dim tables

a-bekkaoui — Fri, 22 Mar 2024 13:52:52 GMT

Hi,

Check this out and tell me if it works, couldn't get hand on your Pbix file unfortunatly but here is the idea:

you'll need to write measures that can calculate across the two fact tables and slice by both date and machine name.

we will focus on measures that aggregate values on the fly.

Use RELATED and RELATEDTABLE functions where necessary to pull in values from related tables, particularly for getting the machine name and reason descriptions.

Use time intelligence functions if needed, to aggregate over time periods like months.

Based on the model and the visual representation you've provided, you might need a measure similar to the following:

Total Value by Reason =
CALCULATE(
SUM(FactTable2[Value]),
TREATAS(
VALUES(DimTable1[DeviceID]),
FactTable1[DeviceID]
),
TREATAS(
VALUES(DimTable3[Date]),
FactTable1[StartDate],
FactTable1[EndDate]
)
)

This measure calculates the total value from FactTable2 for the time windows specified in FactTable1. The TREATAS function is used to treat columns as if they were filters. This can be particularly useful when working with multiple fact tables that don't have a direct relationship but are related through dimension tables. THe core idea is around this logic.

Note: this DAX measure is conceptual, based on the information provided and without knowing the full details of your data model. You may need to adjust field names and logic to fit your exact scenario.

Re: Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Sat, 23 Mar 2024 04:46:32 GMT

@a-bekkaoui
Hi. Thanks for your inputs. I tried facing error. I think i could not implement your suggestion. If you can help me in the PBI file ,that will be great.
I had also uploaded the revised PBIX file with my current visual (generated out of measure from calculated column -but not scalable).

https://drive.google.com/file/d/1bVmbOCBNamuj7mX8ya-wCuFI7Aa6VHMJ/view?usp=sharing
Please suggest by suitable DAX measure using relationship directly on the data set with out any calculated columns.
Being a newbie could not grasp much of your inputs and sorry for the same.

Re: Suitable DAX for model having two fact tables and multiple dim tables

lbendlin — Sat, 23 Mar 2024 21:59:34 GMT

Your DimTable1 still needs to be broken up into separate Device and Meter dimensions.

Re: Suitable DAX for model having two fact tables and multiple dim tables

InsHunter — Mon, 25 Mar 2024 05:02:24 GMT

lbendlin
Hi. Thank u for your response.
I can do that. But I intend to keep this DimTable1 as a mapping table between deviceID and meterID as different parameters are monitored by them but for same machine.I use the "MachineName" field in the mapping table as a slicer.
I can make them separately for meterID and deviceID. You can advise the way forward how to proceed .Thank you...