Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
We have a Power BI table with multiple rows that we'd like to combine into a report with single rows
This is a nonprofit providing case management support for people who need help, especially relevant in these challenging times.
Plus 5 other Document Types (with Descriptions)
The Case Managers need a report on a page or two, that shows a row for each of their 50 cases.
Each row would include a Last Completed Date and Next due Date for each Document Type (currently rows in the table)
I'm assuming that the "QSA Last" field (column) would be a measure that would be some kind of a DAX "lookup" of Last_Completed_Date where
Doc_Type = QSA (Qualified Setting Assessment)
and Case = Case_Number
So many DAX options - not sure which would be best! Would you concatenate Doc_Type and Case_Number in the source dataset to simplify the "lookup"?
Thanks in advance for your coaching!
Solved! Go to Solution.
One way is pivot: https://radacad.com/pivot-and-unpivot-with-power-bi
Another way is to summarize
new table =
Summarize(Table, Table[Case Number], "CGA LAST", maxx(filter(Table, table[Document Desc] ="CGA"),Table[Last completed Date])
, "CGA Next", maxx(filter(Table, table[Document Desc] ="CGA"),Table[Next Due Date])
)
A new table like above. Add more columns as per need.
Hi,
This M code works. You may also download my PBI file from here.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sTtU1VNIBMopS0zPLUosUUssSc0oTSzLz84DCBoa6hoa6RgaGlkqxOkjKA0sTczLTMlNTFIpTS0oy89IVEouLU4uLc1PzSiDaDCzRtBnht8UIi3LCthgaQLXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, #"Document Description" = _t, #"Last completed date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", type text}, {"Document Description", type text}, {"Last completed date", type date}}),
#"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Document Description", Text.Upper, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Document Description", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Document Description", Text.Clean, type text}}),
#"Added Custom" = Table.AddColumn(#"Cleaned Text", "Next due date", each if [Document Description]="CAREGIVER EVALUATION" then Date.AddYears([Last completed date],2) else Date.AddYears([Last completed date],1)),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Case Number", "Document Description"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","CAREGIVER EVALUATION","CGA",Replacer.ReplaceText,{"Document Description"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","QUALIFIED SETTING ASSESSMENT","QSE",Replacer.ReplaceText,{"Document Description"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"Document Description", "Attribute"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Value", type date}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged]), "Merged", "Value")
in
#"Pivoted Column"
Hope this helps.
Hi, @JDBOS
Based on your description, you may create measures as below.
CGA Last =
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
CONCATENATEX('Table','Table'[Last Completed Date],","),
FILTER(
ALLSELECTED('Table'),
'Table'[Case Number] = _casenum&&
'Table'[Document Description] = "Caregiver Evaluation"
)
)
CGA Next =
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
CONCATENATEX('Table','Table'[Next Due Date],","),
FILTER(
ALLSELECTED('Table'),
'Table'[Case Number] = _casenum&&
'Table'[Document Description] = "Caregiver Evaluation"
)
)
QSA Last =
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
CONCATENATEX('Table','Table'[Last Completed Date],","),
FILTER(
ALLSELECTED('Table'),
'Table'[Case Number] = _casenum&&
'Table'[Document Description] = "Qualified Setting Assessment"
)
)
QSA Next =
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
CONCATENATEX('Table','Table'[Next Due Date],","),
FILTER(
ALLSELECTED('Table'),
'Table'[Case Number] = _casenum&&
'Table'[Document Description] = "Caregiver Evaluation"
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @JDBOS
Based on your description, you may create measures as below.
CGA Last =
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
CONCATENATEX('Table','Table'[Last Completed Date],","),
FILTER(
ALLSELECTED('Table'),
'Table'[Case Number] = _casenum&&
'Table'[Document Description] = "Caregiver Evaluation"
)
)
CGA Next =
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
CONCATENATEX('Table','Table'[Next Due Date],","),
FILTER(
ALLSELECTED('Table'),
'Table'[Case Number] = _casenum&&
'Table'[Document Description] = "Caregiver Evaluation"
)
)
QSA Last =
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
CONCATENATEX('Table','Table'[Last Completed Date],","),
FILTER(
ALLSELECTED('Table'),
'Table'[Case Number] = _casenum&&
'Table'[Document Description] = "Qualified Setting Assessment"
)
)
QSA Next =
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
CONCATENATEX('Table','Table'[Next Due Date],","),
FILTER(
ALLSELECTED('Table'),
'Table'[Case Number] = _casenum&&
'Table'[Document Description] = "Caregiver Evaluation"
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
There's so much to learn about DAX! Between the three recommended solutions, plenty of good options and features to understand. Nice use of a variable in the last solution @v-alq-msft - along with Calculate, ConcatenateX, and Filter AllSelected.
Plus Sumarize and Maxx+Filter from @amitchandak along with Pivot.
And Ashish @Ashish_Mathur provided M Code that works (once I learn more about how to use M Code 😉
Thanks for the timely help!
Hi,
This M code works. You may also download my PBI file from here.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sTtU1VNIBMopS0zPLUosUUssSc0oTSzLz84DCBoa6hoa6RgaGlkqxOkjKA0sTczLTMlNTFIpTS0oy89IVEouLU4uLc1PzSiDaDCzRtBnht8UIi3LCthgaQLXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, #"Document Description" = _t, #"Last completed date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", type text}, {"Document Description", type text}, {"Last completed date", type date}}),
#"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Document Description", Text.Upper, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Document Description", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Document Description", Text.Clean, type text}}),
#"Added Custom" = Table.AddColumn(#"Cleaned Text", "Next due date", each if [Document Description]="CAREGIVER EVALUATION" then Date.AddYears([Last completed date],2) else Date.AddYears([Last completed date],1)),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Case Number", "Document Description"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","CAREGIVER EVALUATION","CGA",Replacer.ReplaceText,{"Document Description"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","QUALIFIED SETTING ASSESSMENT","QSE",Replacer.ReplaceText,{"Document Description"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"Document Description", "Attribute"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Value", type date}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged]), "Merged", "Value")
in
#"Pivoted Column"
Hope this helps.
@Ashish_Mathur - thx SO MUCH for PBIX! Clearly your solution works - now I just need to learn about M code and how to work with the source files 😊
As a newcomer to the Power BI/M code world, I'm very impressed. Let me do a bit of background studying and I'll come back to you with questions!
One way is pivot: https://radacad.com/pivot-and-unpivot-with-power-bi
Another way is to summarize
new table =
Summarize(Table, Table[Case Number], "CGA LAST", maxx(filter(Table, table[Document Desc] ="CGA"),Table[Last completed Date])
, "CGA Next", maxx(filter(Table, table[Document Desc] ="CGA"),Table[Next Due Date])
)
A new table like above. Add more columns as per need.
@amitchandak I clearly need to learn more about Pivot - so thanks for the Radacad link
And, let me do some testing with Summarize and Maxx w Filter and will keep you posted
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
86 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
110 | |
97 | |
71 | |
67 |