Dax new measure URGENT

[Sassy]

• Hello all, this is my first time here. I need to do a distinct count and have following; fact table 1 which is connected to 4 dimensions and a fact table 2 which is connected to fact table1 via file number to file number ( many to many). I have the following so far : calculate(distinctcount(facttable1[file number]), filter(facttable1, related(u dim[Unit code]), in { "134445", "134556", "216056}|| RELATED(cdim[cindicator])=1||RELATED (c dim[c type])in {1,2}) how can I add fact table2 (which is connected via ID to dimension u dim its ID ( indirectly)and have it filtered to u dim ([unit code]). Essentially i want the count based on fact table 1 with all the conditions listed aboce as well as fact table 2  and (u dim[Unit code]), in { "134445", "134556", "216056"} Hope  i didn't confuse anyone. 

[Sassy]

Thank you but it didn't work. The last part starting related it gives me a red swigly line  and it doesn't give me the option to choose u dim

[DataNinja777]

Hi @Sassy ,

It is better to avoid relying on many-to-many relationships due to ambiguity. Instead, we can filter FactTable2 explicitly and propagate the filtering to FactTable1. The approach involves filtering FactTable2 based on U Dim[Unit Code], then extracting the relevant File Numbers, and finally using those to filter FactTable1. This ensures that only FactTable1 records with matching File Numbers from the filtered FactTable2 are considered while also applying the necessary conditions from U Dim and C Dim.

Measure_Count = 
VAR Fact2_Filtered =
    FILTER(
        FactTable2,
        LOOKUPVALUE(
            U_Dim[Unit Code], 
            U_Dim[ID], FactTable2[ID]  
        ) IN { "134445", "134556", "216056" }
    )

VAR ValidFileNumbers =
    DISTINCT(
        SELECTCOLUMNS(Fact2_Filtered, "FileNumber", FactTable2[File Number])
    )

RETURN 
CALCULATE(
    DISTINCTCOUNT(FactTable1[File Number]),
    FactTable1[File Number] IN ValidFileNumbers,
    FactTable1[File Number] IN VALUES(FactTable1[File Number]),  
    RELATED(U_Dim[Unit Code]) IN { "134445", "134556", "216056" },
    RELATED(C_Dim[C Indicator]) = 1,
    RELATED(C_Dim[C Type]) IN { 1, 2 }
)

By structuring it this way, we ensure that FactTable2 is filtered first, and only relevant File Numbers are extracted. These are then applied to FactTable1 to keep only matching records. The final count is performed with all conditions applied, ensuring clarity in filtering without relying on implicit many-to-many relationships. 

Best regards,

[Sassy]

I have sql on this but I was trying to replicate in power bi directly by bringing in the tables. I have already transformed the data to keep only 2020 data and onwards. C dim 01, and 02, joined the code and subcode to read as one and filtered to these ones only. I have modified the date as it is bit sensitive. This is the native query : 

SELECT

year(a.reported_dt) as year_reported,

COUNT(DISTINCT a.file_num) as file_count

FROM Fact table1 as a

FULL JOIN u_dim as b on a.v_id = b.id

LEFT JOIN c_type_dim as c on a.i_c_type_id = c.id

LEFT JOIN Fact table2 as d on a.file_num = d.file_num

LEFT JOIN u_dim as e on d.o_type_id = e.id

WHERE a.reported_dt >= '2020-01-01'

AND

(c_ind = '01'

OR c.type in ('01' '02')

OR concat(b.code, b.subcode) in ("134445", "134556", "216056")

OR concat(e.code, e.subcode) in ("134445", "134556", "216056")

AND a.file_num IN (select distinct a.file_num

fact table 1  as a

LEFT JOIN fact table 1 as b on a.file_num = b.file_num

LEFT JOIN u_dim as c on b.o_type_id = c.id

LEFT JOIN o_dim as d on a. unit_id = d.id

[v-junyant-msft · Accepted solution]

Hi @Sassy ,

I created 4 tables of sample data and conducted the following tests:
Sample data:

Here are the relationships:

Use this DAX to create a measure:

DistinctCountWithFilters = 
CALCULATE(
    DISTINCTCOUNT(FactTable1[file_num]),
    FILTER(
        FactTable1,
        RELATED(u_dim[Unit code]) IN { "134445", "134556", "216056" } ||
        RELATED(c_type_dim[c_indicator]) = 1 ||
        RELATED(c_type_dim[c_type]) IN { 1, 2 }
    ),
    FILTER(
        FactTable2,
        RELATED(u_dim[Unit code]) IN { "134445", "134556", "216056" }
    ),
    FactTable1[reported_dt] >= DATE(2020, 1, 1)
)

And the final output is as below:

If it is incorrect, please provide sample data of each table, data model (relationship between tables, data types, etc.) and your expected results, which will help us understand your problem and find the right solution. Thank you!

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