How 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 ) )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-Jun
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?
Custom Legend base don percentile rankings?
hi guys, I was wondering if there is some sort of formula or method to create 4 distinct groups to place my customers in based on 2 unique measures shown below ( Sales Rank & Net Profit Rank). The percentile lines are from the scateer chart visuals and I essentially would like to group my customers into these percentile min and max's but I am unsure how to. For example, a good customer would have Sales Rank >=1061 and NP Rank >=1163 whereas a decent customer would have Salres Rank >= 951 and NP Rank < 1058. Any ideas on how to create this legend?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%
Unable to connect gateway with datasource
I just published my new report with MySQL as data input. I also created a gateway with this same SQL database. Actually I can not connect those 2 together. If I am looking at the settings from my datafield, i can not select a gateway. Refreshing from app.powerbi.com is not possible now. Any idea how to solve this?
