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

[InsHunter]

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.

[a-bekkaoui]

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.

[InsHunter]

@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.

[lbendlin]

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

[InsHunter]

@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...

[InsHunter]

Typo-"apologies"

[InsHunter]

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

[lbendlin]

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.

[InsHunter]

@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.

[lbendlin]

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

[InsHunter]

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.

[InsHunter]

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

Please confirm in order.

[InsHunter]

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

[v-yaningy-msft]

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