Forum Discussion
Need help on showing single row per condition
Hi All,
I need a help , i got a report with below table .
here i have to show only one row if the final status is success and also need to show the failed row too. so one row for success and for failed.
below is the sample data, but in my report along with the below structure, i got many more columns associated with this invoice data.
| Company | Invoie Number | Invoice date | Transferdate | Invoiceupload status | Invoice Final Status |
| Uk | 12 | 30 12 2024 | 30 12 2024 | Invoice Exists | Success |
| UK | 12 | 31 12 2024 | 31 12 2024 | Invoice Exists | Success |
| UK | 12 | 1 01 2025 | 1 01 2025 | Invoice Exists | Success |
| UK | 12 | 2 01 2025 | 2 01 2025 | Invoice Exists | Success |
| US | 1234 | 1 01 2025 | 1 01 2025 | Invoice don't exists | Failed |
| US | 1234 | 1 01 2025 | 2 01 2025 | Invoice Exists | Success |
| AUS | 123444 | 3 10 2023 | 3 10 2023 | Invoice don't exists | Failed |
| AUS | 123444 | 4 10 2023 | 4 10 2023 | Invoice Exists | Success |
Anonymous Go to the "Modeling" tab in Power BI.
Click on "New Table".
Use the following DAX code to create a table that filters the data:DAX
FilteredTable =
UNION(
SELECTCOLUMNS(
FILTER(
'YourTable',
'YourTable'[Invoice Final Status] = "Success"
),
"Company", 'YourTable'[Company],
"Invoice Number", 'YourTable'[Invoice Number],
"Invoice date", 'YourTable'[Invoice date],
"Transferdate", 'YourTable'[Transferdate],
"Invoiceupload status", 'YourTable'[Invoiceupload status],
"Invoice Final Status", 'YourTable'[Invoice Final Status]
),
SELECTCOLUMNS(
FILTER(
'YourTable',
'YourTable'[Invoice Final Status] = "Failed"
),
"Company", 'YourTable'[Company],
"Invoice Number", 'YourTable'[Invoice Number],
"Invoice date", 'YourTable'[Invoice date],
"Transferdate", 'YourTable'[Transferdate],
"Invoiceupload status", 'YourTable'[Invoiceupload status],
"Invoice Final Status", 'YourTable'[Invoice Final Status]
)
)
4 Replies
Hi bhanu_gautam
Thanks for replying, but this works with smaller table, my table got around 30 plus columns associated with invoice and final status.
what i tried worked out for me is i set the transfer date to show only latest in the field settings and it worked.
- bhanu_gautam
Super User
Anonymous Go to the "Modeling" tab in Power BI.
Click on "New Table".
Use the following DAX code to create a table that filters the data:DAX
FilteredTable =
UNION(
SELECTCOLUMNS(
FILTER(
'YourTable',
'YourTable'[Invoice Final Status] = "Success"
),
"Company", 'YourTable'[Company],
"Invoice Number", 'YourTable'[Invoice Number],
"Invoice date", 'YourTable'[Invoice date],
"Transferdate", 'YourTable'[Transferdate],
"Invoiceupload status", 'YourTable'[Invoiceupload status],
"Invoice Final Status", 'YourTable'[Invoice Final Status]
),
SELECTCOLUMNS(
FILTER(
'YourTable',
'YourTable'[Invoice Final Status] = "Failed"
),
"Company", 'YourTable'[Company],
"Invoice Number", 'YourTable'[Invoice Number],
"Invoice date", 'YourTable'[Invoice date],
"Transferdate", 'YourTable'[Transferdate],
"Invoiceupload status", 'YourTable'[Invoiceupload status],
"Invoice Final Status", 'YourTable'[Invoice Final Status]
)
) Hi Anonymous ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
v-sgandrathiCommunity Support
Hi Anonymous
I'm glad you found a solution and resloved the query. Thank you very much for sharing here.
Kindly mark your reply as the accepted solution so that others in the community can find it quickly.
Thankyou for connecting with Microsoft Community Forum.