User Profile

ymirza

Helper II

Joined 8 years ago

31 Posts2 LikesLikes received1 Solution

View All Badges

User Widgets

Contributions

Re: Distinct count from a semicolon separated string and reversing the data
Excellent steps. My job was done till Removed Columns. Many thanks 🙂 let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjJU0lFyNrR2NrJ2NlGK1QGKGIFEgFxjCNcYqgAqawLmKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Risk = _t, Control = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Risk", type text}, {"Control", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Control", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Control.1", "Control.2", "Control.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Control.1", type text}, {"Control.2", type text}, {"Control.3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Risk"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
4 years ago Place DAX Commands and Tips
1.3KViews
0likes
0Comments
Distinct count from a semicolon separated string and reversing the data
Hello PBI Community I have this dataset with multiple strings separated by semi-colons. Risks Controls Risk 1 Control 1; Control 13; Control 6; Control 8 Risk 2 Control 1; Control 2; Control 10 Risk 3 Control 1; Control 13; Control 8 I want to achieve a Distinct count of these controls; for example, the above should show 6 unique controls. Distinct Count of Controls 6 Secondly, I want to reverse engineer the same dataset into something like this. Control Risks Control 1 Risk 1; Risk 2; Risk 3 Control 2 Risk 2 Control 6 Risk 1 Control 8 Risk 1; Risk 3 Control 10 Risk 2 Control 13 Risk 1: Risk 3 Any help would be highly appreciated 🙂
Solved
4 years ago Place DAX Commands and Tips
DAX Commands and Tips
1.4KViews
0likes
2Comments
How to get a calculated column of Current, Past Due, Near-Due Date from a column of Due Dates?
I have a Due Date column, and I want a filter that should show "Current", "Past Due", and " Near-Due Date". When the Actual Due date is near 30 days (or more) from today(), it should show "Near-Due Date" I tried a calculated column, but it only returns "Current" and "Past Due" Assignment Due Status = IF(DATEDIFF('TABLE'[AssignmentDueDate], TODAY(), DAY)<0, "Current", IF(DATEDIFF('TABLE'[AssignmentDueDate], TODAY(), DAY)>=0, "Past Due", IF(DATEDIFF('TABLE'[AssignmentDueDate], TODAY(), DAY)<= 30, "Near Due"))) Please help!
Solved
5 years ago Place Desktop
Need Help
729Views
0likes
1Comment
Re: Percentage of False in a column from a Month row
Some of the months are not covered with TRUE statements, that is why the DAX you shared was effective only at individual row level, but not the Grant total. What I did using your DAX is created another measure Total % = IF(HASONEVALUE(Sheet1[Month]),[PercMeasure], AVERAGEX(VALUES(Sheet1[Month]),[PercMeasure]))
6 years ago Place Desktop
1.8KViews
0likes
0Comments
Re: Percentage of False in a column from a Month row
As a start this is good, I am getting individual measures correct for each month. but the grand total is calculating on all rows instead of specific rows. for e.g. if Month of Feb has all False, filter is still using Feb to divide the grant total. Is there any DAX that only calculates the TRUE from each month and returns grand total based on the TRUE found in those months?
6 years ago Place Desktop
1.8KViews
0likes
3Comments
Percentage of False in a column from a Month row
I have table with name, month and rate. What would be the appropriate DAX to measure percentage of False only from that specifc month (not the total column of Rate)? for e.g. Jan has 6 names and 3 false. should return 50% under Jan. Month Name Rate Jan Adam TRUE Jan Mark FALSE Jan Jane TRUE Jan Jacob FALSE Jan Steven TRUE Jan Jones FALSE Feb Adam TRUE Feb Mark FALSE Feb Jane TRUE Feb Jacob FALSE Feb Steven TRUE Feb Jones FALSE Mar Adam TRUE Mar Mark FALSE Mar Jane TRUE Mar Jacob FALSE Mar Steven TRUE Mar Jones FALSE
Solved
6 years ago Place Desktop
Need Help
1.8KViews
1like
5Comments
Re: Multiple Currencies in a single column converted to Euro or Dollar
the format of the column was the problem. It loaded as Whole number from Excel. When I changed it to Decimal Number in Query Editor your solution worked. thanks
6 years ago Place Desktop
4.1KViews
0likes
0Comments
Re: Multiple Currencies in a single column converted to Euro or Dollar

6 years ago Place Desktop
4.2KViews
0likes
2Comments
Re: Multiple Currencies in a single column converted to Euro or Dollar
It is returning huge number. Probably it is adding to the SUMX equation? Measure used: Cost Eur = SUMX(Data,Data[Cost]*RELATED(Rates[Rate])) Even tried this: Cost Eur = SUMX(Data,Data[Cost] / RELATED(Rates[Rate])) returns
6 years ago Place Desktop
4.2KViews
0likes
4Comments
Multiple Currencies in a single column converted to Euro or Dollar
I have this report with Cost of Assets purchased using different currencies. I would like to have one Column which will automatically convert them to Euro rate from another table. What is the appropriate measure? Currency Cost YTL 19,571.79 USD 5,990.60 MAD 0.00 YER 126,000.00 USD 2,314.55 SAR 5,500.00 MAD 6,800.00 NGN 50,193.20 KES 638,812.35 YER 35,750.00 USD 8,329.00 SAR 5,500.00 Currency Rate DZD 142.9654 EGP 23.83123 EUR 1 IRR 155989.4 JOD 0.858257 KES 130.0549 KWD 0.361328 LBP 1815.732 MAD 11.70195 NGN 484.6802 PKR 153.499 SAR 4.497835 SEK 10.48924 TND 3.745863 YTL 9.394526 AED 4.400503 GBP 0.90639 USD 1.197867 YER 300.6269
Solved
6 years ago Place Desktop
Need Help
4.2KViews
0likes
6Comments

Data Privacy

Microsoft Fabric Community and Privacy

To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.