Change Data Type for Column Chart with changing filter
Hello, I am trying to visualize multiple KPIs on the same column chart and you can navigate through the different KPIs by choosing the desired KPI from a slicer. However, the KPIs aren't all the same unit (some are percentages, some are currency, and some are absolute values), the column chart y-axis unit doesn't change with choosing the KPI, instead it remains the same for the first data I place in the "column y-axis" tab. I want them to change with the change of selection from slicer. To clarify, as seen in first two pictures, I placed MC Efficiency (%) in the first slot above GGA(grams) and the tables are showing percentages because the data type of MC Efficiency is set to percentage. In picture 3&4 When I switch the positions of the MC Efficiency and GGA, placing the GGA above, all the values change to absolute and no percentages are shown. How do I overcome this
Writing measure to show multiple visuals on the same chart with one slicer
Hello, I am sorry to keep posting looking for a solution on this. I am trying to write a measure. I have tried multiple methods but cant get anything to work. I would assume having two different measures on the same visual would work. I have two tables (Table1 and Table2). My goal is to use one slicer and show multiple visuals from the same table on the same chart. For example, if a month is selected, I want to show data (filtered with different conditions) for that month, and the month most recent to the selected month. Column 'Table1'[Month] is linked to Column 'Table2'[Month] with a one to many relationship (Table2-one, Table1-many). Then I have a slicer that uses column 'Table2'[Month]. The visual for my current month works, but now I am trying the add the data to the same visual for its previous month. For the previous month, If the user selects a month with the slicer, I want to save the [Previous Month] associated in Table2 column as text. So if 24-Jun is selected, I want to save "24-May" as text. Then I want to filter and count the number of rows in Table1 where [Month End] = the saved text & Type = "Task". So in this example, I would count the number of rows in Table1 where Table1[Month End] = "24-May" & Type = "Task", and this would return 1. Table1 ID Status Date Rank Previous Status Month Type Group Previous Month A Normal 3 No previous 24-Mar Task <0 No previous B Normal 3 No previous 24-Mar Task 1 to 5 No previous C Not Normal 3 No previous 24-Mar Task 1 to 5 No previous D Not Normal 3 No Previous 24-Mar Not Task 11 to 15 No previous A Not Normal 2 Normal 24-May Task 6 to 10 24-Mar B Normal 2 Normal 24-May Not Task 6 to 10 24-Mar C Normal 2 Not Normal 24-May Not Task 6 to 10 24-Mar D Not Normal 2 Not Normal 24-May Task 6 to 10 24-Mar A Not Normal 1 Not Normal 24-Jun Task 1 to 5 24-May B Not Normal 1 Normal 24-Jun Task <0 24-May D Normal 1 Normal 24-Jun Task <0 24-May E Normal 1 24-Jun Task 1 to 5 24-May Table2 Date Rank Month Next Date Rank Previous Month 3 24-Mar 4 2 24-May 3 24-Mar 1 24-Jun 2 24-May Sample of visual I'm trying to create: Measure that doesn't work for previous month: (I keep getting blank when selecting a current month) VAR SelectedMonth = SELECTEDVALUE('Table2'[Month]) VAR PreviousMonth1 = CALCULATE( MAX('Table2'[Previous Month]), FILTER( ALL('Table2'), 'Table2'[Month] = SelectedMonth ) ) VAR CountRows1 = CALCULATETABLE( 'Table1', FILTER( ALLSELECTED('Table1'), 'Table1'[Month] = PreviousMonth1 && 'Table1'[Type] = "Task" ) ) RETURN COUNTROWS(CountRows1) Measure works for selected month: VAR SelectedMonth = CALCULATE( MAX('Table1'[Date Rank]), FILTER( ALL('Table1'), 'Table1'[Month] = SELECTEDVALUE('Table2'[Month]) ) ) VAR _tablecalculation = CALCULATETABLE( 'Table1', ALLSELECTED('Table2'), 'Table1'[Date Rank] = SelectedMonth, 'Table1'[Status] = "Not Normal", 'Table1'[Previous Status] = "Not Normal", 'Table1'[Type] = "Task" ) RETURN COUNTROWS(_tablecalculation) Any help in the right direction is definetly appreciated!
okay icon
I have the following table. And I have the following matrix look The "Planned" measure is: CALCULATE( SUM( fac_NFs_SFs[valor_liquido_nf] ), USERELATIONSHIP( dim_Calendario[Data], fac_NFs_SFs[data_previsao_recebimento] ) ) The "Achieved" measure is: CALCULATE( SUM( fac_NFs_SFs[valor_liquido_nf] ), USERELATIONSHIP( dim_Calendario[Data], fac_NFs_SFs[data_recebimento] ) ) The months "Mar", "Mai" and "Jun" = dim_calendario[mês] I need that when I have a receipt date, all the month contexts have the Okay icon, ignoring the month of the column context. I can't do this because the forecast sum uses the forecast column and the received sum uses the received date column. What do I do?
Time duration is equal 24:00:00 or more does not working
Hello community, me visualize machine's different performance categories events like runtime,planned stoppages,unplanned stoppages and etc using this formula. Duration_Formatted = FORMAT(SUMX(ADDCOLUMNS('Machine_Performance',"decimalVal",CONVERT('Machine_Performance'[Duration],DOUBLE)),[decimalVal]),"HH:MM:SS") Problem is that if duration is equal 24:00:00 the formula inputs 00:00:00 instead and formula can not sum more than 24 hours also. Is it possible to show time as i needed and summarize duration?Dynamically create months-year in a table
Hello Community, I have a Date filter table where i have created values based of parameter: Most recent being the full previous month and then showing each month prior to that The calculation is correct however my sort order is incorrect: its starts from Most Recent (Current calender month Jun24), May 23 Jun 23 July 23 etc which is wrong. MY sort order should be Most recent = 1, May 24 = 2, Apr 24 = 3, Mar 24 = 4, Feb 24 = 5, Jan 24 = 6, Dec 23 = 7, Nov 23 = 8, Oct 23 = 9, Sept 23 = 10, Aug 23 = 11, Jul 23 = 12, Jun 23 = 13, May 23 = 14 etc Same file can be access here: Sample File DIM Date Filter = VAR _latest = MAX('DIM Calendar (Ranking)'[Date]) VAR _oldest = DATE(2023, 5, 22) -- Specific start date VAR _previousYearStart = DATE(YEAR(_latest) - 1, 1, 1) VAR _previousYearEnd = DATE(YEAR(_latest) - 1, 12, 31) VAR _startOfPreviousMonth = EOMONTH(_latest, -2) + 1 VAR _endOfPreviousMonth = EOMONTH(_latest, -1) VAR _endOfPreviousMonth2 = EOMONTH(_latest, -2) VAR _startDate = DATE(YEAR(_oldest), MONTH(_oldest), 1) RETURN UNION( ADDCOLUMNS( CALENDAR(_startOfPreviousMonth, _endOfPreviousMonth), "Date Periods", "Most Recent Month", "Order", 1 ), ADDCOLUMNS( CALENDAR(_startDate, _endOfPreviousMonth2), "Date Periods", FORMAT([Date], "MMM YY"), "Order", DATEDIFF(_startDate, EOMONTH([Date], 0), MONTH) + 2 ), ADDCOLUMNS( CALENDAR(DATE(YEAR(_latest), 1, 1), _latest), "Date Periods","YTD", "Order", DATEDIFF(_startDate, _latest, MONTH) + 3 ), -- Dynamically generate quarters for the current year ADDCOLUMNS( FILTER( CALENDAR(DATE(YEAR(_latest), 1, 1), _latest), MONTH([Date]) <= MONTH(_latest) ), "Date Periods", "Q" & FORMAT([Date], "Q YYYY"), "Order", DATEDIFF(_startDate, _latest, MONTH) + 4 + QUARTER([Date]) - 1 ), -- Dynamically generate quarters for the previous year ADDCOLUMNS( FILTER( CALENDAR(_previousYearStart, _previousYearEnd), MONTH([Date]) <= 12 && YEAR([Date]) = YEAR(_previousYearStart) ), "Date Periods", "Q" & FORMAT([Date], "Q YYYY"), "Order", DATEDIFF(_startDate, _latest, MONTH) + 8 + QUARTER([Date]) - 1 ), -- Additional hardcoded years or custom periods can be added here ADDCOLUMNS( CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31)), "Date Periods", "2023", "Order", DATEDIFF(_startDate, _latest, MONTH) + 12 ), -- Add custom periods if needed ADDCOLUMNS( CALENDAR(_oldest, _latest), "Date Periods", "Custom", "Order", DATEDIFF(_startDate, _latest, MONTH) + 13 ) ) thanks ViralNeed help in creating Dax from the Excel formula
IF(AND(A1<0,B2<>""),"SMU",IF(AND(A1>0,B2<>""),"SMU","MUJ")) Dear Friends, I am struggling in converting the above excel function into a dax in Power Bi. Can anyone please help me in writing the dax query from the function above. It would be a great help and learning for me. Thanks in advance.
