How to filter table with value related to the selectedvalue
I have the two tables below in my power bi file. I have a slicer from Table2 that uses column [Month] as the selected value. If a month is selected, I want to calculate and filter Table1 where Table1 = the associated selected [Date Rank] from Table2 + 1. For example, if 24-Jun is selected from Table2, I want to filter Table1 where [Month] = 24-May. Table1 ID Status Date Rank Previous Status Month Type Group A Normal 3 No previous 24-Mar Task <0 B Normal 3 No previous 24-Mar Task 1 to 5 C Not Normal 3 No previous 24-Mar Task 1 to 5 D Not Normal 3 No Previous 24-Mar Not Task 11 to 15 A Not Normal 2 Normal 24-May Task 6 to 10 B Normal 2 Normal 24-May Not Task 6 to 10 C Normal 2 Not Normal 24-May Not Task 6 to 10 D Not Normal 2 Not Normal 24-May Task 6 to 10 A Not Normal 1 Not Normal 24-Jun Task 1 to 5 B Not Normal 1 Normal 24-Jun Task <0 D Normal 1 Normal 24-Jun Task <0 E Normal 1 24-Jun Task 1 to 5 Table2 Date Rank Month 3 24-Mar 2 24-May 1 24-JunHow to count rows of filtered table based on slicer selection
I have the following table below. The months are ordered according to the [Date Rank] column with 1 being the most current month. I have a 1 measure that selects the previous month. So if March is selected, the measure is 3. If Apr is selected, the measure is 2. How can I make a measure to count the number of items/rows in the previous month and place in a card visual. So if Apr is selected as the current month, I want to count the number of rows where Date Rank = 2. And the answer would be 5 since there are 5 rows for march. I also have the following code below, but it looks like my current CALCULATE(COUNTROWS statement does not work because every time I select a month with a filter, the car visual just shows blank. It works when I pass the function a number, but not with the variable. Thank you for any help on solving this. ID Status Date Rank Month A Not Late 3 Jan B Not Late 3 Jan C Finished 3 Jan D Not Late 3 Jan A Not Late 2 March B Re-opened 2 March C Late 2 March D Late 2 March E Late 2 March A Late 1 Apr B Late 1 Apr C Late 1 Apr Slicer I have to select month: First measure I have to select the date rank of the previous month: Daterankofpreviousmonth = VAR currentselecteddaterank = SELECTEDVALUE('Table'[Date Rank]) RETURN IF( ISBLANK(currentselecteddaterank), BLANK(), currentselecteddaterank + 1 ) My current measure to count the number of rows where Table[Date Rank] = daterankofpreviousmonth. But this does not work. The card visual just shows blank. Countprevious = VAR currentselecteddaterank = [Daterankofpreviousmonth] RETURN IF( ISBLANK(currentselecteddaterank), BLANK(), CALCULATE( COUNTROWS('Table'), 'Table'[Date Rank] = currentselecteddaterank ) )
SUM/SUMIF Function In DAX
Please assist. Am i using the correct function in dax for SUM/SUMIF equilvalents in Dax, Also when do i use Calculate? My Totals are inflated, my conversions from Excel to DAX, might not be correct. Measure: Depreciation and amortisation in Excel(see Excel link for Formula), DAX: WorkingCapitalMvtsInclProvisions = [Other asset movements] + [Working capital - budget flex] + CALCULATE( SUM('ZTBR'[Amount in USD]), 'ZTBR'[Roll_Up_Function] = "Provision movements" )<p>Formula in Excel: <li-code lang="markup">=-SUMIF(Details!B:B;'Cash Flow'!G39;Details!G:G) AND Disposals & impairment of fixed assets. DAX: Disposals & impairment of fixed assets = CALCULATE( SUM('ZTBR'[Amount in USD]), 'ZTBR'[Roll_Up_Function] IN { "Profit on disposal of pooling equipment", "Scrapped pooling equipment", "Impairment or valuation adjustment of pooling equipment", "Disposals or valuation adjustments of other fixed assets" } ) Excel Formula: =SUM(K21:K24) PBIX: https://drive.google.com/file/d/1-wHE0e50nSM9-wSq4dTxStDX58APO_MI/view?usp=sharing Excel: https://docs.google.com/spreadsheets/d/1ubo9TAz2zzCpKbfO5SFBMjFLgjFtUxhY/edit?usp=sharing&ouid=104129043494164133703&rtpof=true&sd=true
RLS_DAX_ of two disconnected table using wildcard
Hi, I am facing some issue while implementing the RLS for below fact table. main_fact_table job_no year IT123 2023 IT123467 2024 IT235 2023 IT2345 2024 IT345 2023 IT334 2024 IT3647 2023 IT3654 2024 IT6584 2023 IT6574 2024 user_access user name job_no year sourav IT12 2023 jeet IT6 2023 kunal IT6 2024 sourav IT3 2024 jeet IT2 2023 kunal IT1 2024 rupam IT3 2024 rupam IT6 2023 as i mentioned there is two table. 1)main_fact_table (this is the main facr table) 2)user_access (this is the details of user who has access to the perticuler data) these two table are disconnected table hence they have no relation between them, and we are using the coloumn "job_no" (user_access) to decide the access for a perticuler user but in the "user_access" table column "job_no" dose not contain the full value we need to implement wildcard here for example in user_access one user named "sourav" has access to the row job_no in fact table whose value starts with "IT12" AND "IT3" so sourav should she all the rows in "main_fact_table" whose "job_no" starts with "IT12" AND "IT3" and corrosponding year also should take in considaration in this case sourav should see in "main_fact_table" as below shown job_no year IT123 2023 IT334 2024Net debt increase/(decrease) Calculation
I am trying to display row Net debt increase/(decrease) and assign the total value of: Net Debt Increase/(Decrease) = VAR LeasesPrincipalRepaid = CALCULATE( SUM('Bracs'[Metric Value ($)]), 'Bracs'[Metric] IN {"Leases principal repaid", "Change in cash net of overdraft"} ) RETURN LeasesPrincipalRepaid So that it display when using this code: New CombinedAmount = VAR SelectedCashFlowDescription = SELECTEDVALUE('CashflowDiscriptions'[description], "None") VAR _TotalNetTaxPaid = CALCULATE([NetTaxPaid], ALL(CashflowDiscriptions)) VAR _NetDebtIncreaseDecrease = CALCULATE( SUM('Bracs'[Metric Value ($)]), 'Bracs'[Metric] IN {"Leases principal repaid", "Change in cash net of overdraft"} ) RETURN SWITCH( TRUE(), SelectedCashFlowDescription = "Interco capital returned", 678, SelectedCashFlowDescription = "Net tax paid", _TotalNetTaxPaid, SelectedCashFlowDescription = "Leases principal repaid", CALCULATE( SUM('Bracs'[Metric Value ($)]), 'Bracs'[Metric] = "Leases principal repaid" ), SelectedCashFlowDescription = "Net debt increase/(decrease)", _NetDebtIncreaseDecrease, CALCULATE( SUM('ZTBR'[Amount in USD]), 'ZTBR'[Roll_Up_Function] IN { "Cash flow from ops - management", "Cash flow from trading", "Profit before Brambles allocations Total", "Depreciation and amortisation", "IPEP expense", "Disposals & impairment of fixed assets", "Profit on disposal of pooling equipment", "Scrapped pooling equipment", "Impairment or valuation adjustment of pooling equipment", "Disposals or valuation adjustments of other fixed assets", "Other cash flow from trading adjustments", "Share-based payments expense", "Working capital mvts excl. provisions", "Debtor movements", "Creditor movements", "Inventory movements", "Prepayment movements", "Provision movements", "Change in capex creditors", "Brambles allocations not in mgt cash flow", "Interco interest and guarantee fees", "Interco cash flows", "Interco royalties", "Statutory reallocations", "Internal restructuring", "Interco dividends Total", "Change in interco balances", "Change in interco recharge clearing", "FX on interco debt", "Interco capital returned", "Interco cash flow adjustments", "Interest expense Total", "Interest revenue", "Interest received", "Lease interest" } ) + CALCULATE( SUM('Bracs'[Metric Value ($)]), 'Bracs'[Metric] IN { "Pension plan adjustment", "Working capital - budget flex", "Pooling equipment additions", "Pooling equipment replacements", "Pooling equipment internal transfers", "Other PP&E additions", "Other PP&E replacements", "Other PP&E internal transfers", "Joint venture loans", "WDV pooling equip. disposals & write-offs", "Gain pooling equip. disposals & write-offs", "WDV other PP&E disposals", "Profit other PP&E disposals", "Discount unwind on long term provisions", "Tax paid", "Tax refunded", "Fiscal unity tax transfers", "Other cash flow items", "FX adjustments to cash flow", "Change in cash net of overdraft" } ) ) Row should disply in the Decsription and the value to match is: Please assist. PBIXhttps://drive.google.com/file/d/1nSBwCKqxdEYe-pmK9UX2nPDV7XOpinfO/view?usp=sharing Please Note, all column descriptions can be found in table CashflowDiscriptions
CUSTOMER SEGMENTATION AGAINST ORDER RANKING
Hi Team, I have two tables - Customers and Orders. These tables are connected via the customer_id via a one-to-many relationship. I created a calculated column in my customer's table named Redeemer_Status where customers are segmented into two - REDEEMER1 and REDEEMER2. Redeemer_Status = VAR _CustomerID = customers[id] VAR _HC_1 = CALCULATE(MAX(orders[with HC]), FILTER(orders, orders[_Order Ranking] = 1 && orders[customer_id] = _CustomerID)) VAR _SignUpOrigin_1 = CALCULATE(MAX(orders[SignUp_Origin]), FILTER(orders, orders[_Order Ranking] = 1 && orders[customer_id] = _CustomerID)) VAR _HC_2 = CALCULATE(MAX(orders[with HC]), FILTER(orders, orders[_Order Ranking] = 2 && orders[customer_id] = _CustomerID)) VAR _SignUpOrigin_2 = CALCULATE(MAX(orders[SignUp_Origin]), FILTER(orders, orders[_Order Ranking] = 2 && orders[customer_id] = _CustomerID)) RETURN IF( _HC_1 = 1 && (_SignUpOrigin_1 = "WHATSAPP_BOT" || _SignUpOrigin_1 = "MOBILE_APP"), "REDEEMER1", IF( _HC_1 = 0 && (_SignUpOrigin_1 = "WHATSAPP_BOT" || _SignUpOrigin_1 = "MOBILE_APP") && _HC_2 = 0 && (_SignUpOrigin_2 = "WHATSAPP_BOT" || _SignUpOrigin_2 = "MOBILE_APP"), "NON-REDEEMER", IF( _HC_2 = 1 && (_SignUpOrigin_2 = "WHATSAPP_BOT" || _SignUpOrigin_2 = "MOBILE_APP") && _HC_1 = 0 && (_SignUpOrigin_1 = "WHATSAPP_BOT" || _SignUpOrigin_1 = "MOBILE_APP"), "REDEEMER2", BLANK() ) ) ) I created a stacked column chart where the column _Order Ranking in my orders table serves as the x-axis and customer_id (Unique) in my Y-axis. I then use the Redeemer_Status column as the legend but filtered it with only the REDEEMER1 and REDEEMER2. I am so confused as to why there are REDEEMER2 that show up in the order ranking 1 instead they should only start to appear in the 2nd order onwards. In this graph, there should be no REDEEMER2 in the column for order rank 1. The 141 REDEEMER2 is incorrect and should not appear there. What did I do wrong?DAX for Remaining Percent Left and Reflect in Bar Graph
Hi Power BI Experts. I would like to ask your help please for my bar chart in the bottom. I want to to reflect in the bottom bar chart the remaining percent left. As you can see in the upper bar chart it is more than 100% (in my chart I use decimal) which 1. 4. I want to reflect the in the bottom chart that the remaining percent left is -40% (or -0.4). As you can see, only the 40% in the upper chart is subtracted to 100%. The bottom chart should supposed to be calculate the 100% and the 40%. In mathematical sense, the computation is 100% maximum percent - 40% percent Project A - 100% in Project B = -40% I want to know how to put a DAX to have a result of the "Remaining Percent" column in my data so I cannot put it manually. When the end date of the project comes, it will subtract to the total assigned percent. MonthAssigned ResourceProjectStart DateEnd DateMaximum PercentCapacity AssignedRemaining Percent 01/07/2022 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/08/2022 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/09/2022 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/10/2022 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/11/2022 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/12/2022 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/01/2023 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/02/2023 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/03/2023 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/04/2023 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/05/2023 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/06/2023 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/07/2023 Employee A Project A 22/07/2022 31/07/2023 100% 40% 60% 01/09/2022 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/10/2022 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/11/2022 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/12/2022 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/01/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/02/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/03/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/04/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/05/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/06/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% -40% 01/07/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% 0% 01/08/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% 0% 01/09/2023 Employee A Project B 01/09/2022 01/09/2023 100% 100% 0%
