The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
This is something that I have struggled with many times in the past and I still quite wrap my head around how to resolve.
This is what I have put together:
EVALUATE
VAR _Working_Table =
ADDCOLUMNS(
Current_Entries,
"Concatenation", [centre] & [ass_code] & [com_id],
"Absent_Ind", RELATED(Current_Active_Marks[absent])
)
VAR _Summarized_Table =
SUMMARIZE(
_Working_Table,
[Concatenation]
)
VAR _Added_Columns =
ADDCOLUMNS(
_Summarized_Table,
"Total_Cands",
CALCULATE(
COUNTROWS(_Working_Table),
FILTER(
_Working_Table,
_Working_Table[Concatenation] = EARLIER([Concatenation])
)
),
"Absent_Count",
CALCULATE(
COUNTROWS(_Working_Table),
FILTER(
_Working_Table,
_Working_Table[Concatenation] = EARLIER([Concatenation]) &&
_Working_Table[Absent_Ind] = "A"
)
)
)
VAR _Absent_Count =
ADDCOLUMNS(
_Added_Columns,
"All_Absent",
IF(
[Total_Cands] = [Absent_Count],
"Yes",
"No"
)
)
RETURN
COUNTROWS(
FILTER(
_Absent_Count,
[All_Absent] = "No"
)
)
The specific part that I am having an issue with is here:
VAR _Added_Columns =
ADDCOLUMNS(
_Summarized_Table,
"Total_Cands",
CALCULATE(
COUNTROWS(_Working_Table),
FILTER(
_Working_Table,
_Working_Table[Concatenation] = EARLIER([Concatenation])
)
),
"Absent_Count",
CALCULATE(
COUNTROWS(_Working_Table),
FILTER(
_Working_Table,
_Working_Table[Concatenation] = EARLIER([Concatenation]) &&
_Working_Table[Absent_Ind] = "A"
)
)
)
Specifically in the CALCULATE when trying do do the following:
Solved! Go to Solution.
I still don't understand how DAX works in this context and how to referenced columns created with ADDCOLUMN, but after rubbing my two brain cells together, I managed to solve my issue by doing the following:
Expected_Entries =
VAR _Summarized_Table =
SUMMARIZE(
Current_Entries,
[centre],
[ass_code],
[com_id]
)
VAR _Final_Table =
ADDCOLUMNS(
_Summarized_Table,
"All_Absent",
IF(
CALCULATE(
COUNTROWS(Current_Entries),
Current_Entries[centre] = EARLIER([centre]),
Current_Entries[ass_code] = EARLIER([ass_code]),
Current_Entries[com_id] = EARLIER([com_id])
)
=
CALCULATE(
COUNTROWS(Current_Active_Marks),
Current_Active_Marks[centre] = EARLIER([centre]),
Current_Active_Marks[ass_code] = EARLIER([ass_code]),
Current_Active_Marks[com_id] = EARLIER([com_id]),
Current_Active_Marks[absent] = "A"
),
"Yes",
"No"
)
)
RETURN
COUNTROWS(
FILTER(
_Final_Table,
[All_Absent] = "No"
)
)
I still don't understand how DAX works in this context and how to referenced columns created with ADDCOLUMN, but after rubbing my two brain cells together, I managed to solve my issue by doing the following:
Expected_Entries =
VAR _Summarized_Table =
SUMMARIZE(
Current_Entries,
[centre],
[ass_code],
[com_id]
)
VAR _Final_Table =
ADDCOLUMNS(
_Summarized_Table,
"All_Absent",
IF(
CALCULATE(
COUNTROWS(Current_Entries),
Current_Entries[centre] = EARLIER([centre]),
Current_Entries[ass_code] = EARLIER([ass_code]),
Current_Entries[com_id] = EARLIER([com_id])
)
=
CALCULATE(
COUNTROWS(Current_Active_Marks),
Current_Active_Marks[centre] = EARLIER([centre]),
Current_Active_Marks[ass_code] = EARLIER([ass_code]),
Current_Active_Marks[com_id] = EARLIER([com_id]),
Current_Active_Marks[absent] = "A"
),
"Yes",
"No"
)
)
RETURN
COUNTROWS(
FILTER(
_Final_Table,
[All_Absent] = "No"
)
)
User | Count |
---|---|
17 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
9 | |
8 |