chart

17 Topics

Dynamic Pareto chart - with field parameters not working
Hello, I have a working Pareto measure with one "sub category" working, but I can't change that to dynamic working with a field parameter here is the measure Pareto % = IF( 1, //ISINSCOPE(par_stop_category[par_stop_category Name]), VAR __AllselectedStops = ALLSELECTED(dim_stop_code[hrm_description]) VAR __StopTable = ADDCOLUMNS(__AllselectedStops, "@Count", [Stops #]) VAR __CurrentCount = [Stops #] VAR __CumStopTable = FILTER(__StopTable, [@Count] >= __CurrentCount) VAR __CumStop = SUMX( __CumStopTable, [@Count]) VAR __AllSelectedStopsCount = CALCULATE([Stops #], __AllselectedStops) VAR __Result = DIVIDE(__CumStop, __AllSelectedStopsCount) RETURN IF(__CurrentCount > 0, __Result, BLANK()) ) my field parameter look like this par_stop_category = { ("Main", NAMEOF('dim_stop_code'[mn_description]), 1,"Main"), ("Harm", NAMEOF('dim_stop_code'[hrm_description]), 0,"Harm"), ("Place", NAMEOF('dim_stop_code'[plc_description]), 2,"Place"), ("Reason", NAMEOF('dim_stop_code'[rsn_description]), 3,"Reason"), ("Code", NAMEOF('dim_stop_code'[code_description]), 5,"Code"), ("Category", NAMEOF('dim_stop_code'[ctgry_description]), -1,"Category"), ("Group", NAMEOF('dim_stop_code'[grp_description]), 1,"Group"), //ERROR ("Error Place", NAMEOF('dim_error_code'[erplc_description]), 8,"Error Place"), ("Error Code", NAMEOF('dim_error_code'[errcd_description]), 9,"Error Code") } both are working fine with themselfes, but when I try to create a dynamic pareto, it's not working any more cause of the error "The ADDCOLUMNS function expects a table expression for argument '', but a string or numeric expression was used." here my dynamic Pareto Measure Pareto % = IF( 1, //ISINSCOPE(par_stop_category[par_stop_category Name]), // VAR __AllselectedStops = ALLSELECTED(dim_stop_code[hrm_description]) VAR __AllselectedStops = SWITCH( SELECTEDVALUE(par_stop_category[par_stop_category Name]), "Main", ALLSELECTED(dim_stop_code[mn_description]), "Harm", ALLSELECTED(dim_stop_code[hrm_description]), "Place", ALLSELECTED(dim_stop_code[plc_description]), "Reason", ALLSELECTED(dim_stop_code[rsn_description]), "Code", ALLSELECTED(dim_stop_code[code_description]), "Category", ALLSELECTED(dim_stop_code[ctgry_description]), "Group", ALLSELECTED(dim_stop_code[mn_description]), "Error Place", ALLSELECTED(dim_error_code[erplc_description]), "Error Code", ALLSELECTED(dim_error_code[errcd_description]) ) VAR __StopTable = ADDCOLUMNS(__AllselectedStops, "@Count", [Stops #]) VAR __CurrentCount = [Stops #] VAR __CumStopTable = FILTER(__StopTable, [@Count] >= __CurrentCount) VAR __CumStop = SUMX( __CumStopTable, [@Count]) VAR __AllSelectedStopsCount = CALCULATE([Stops #], __AllselectedStops) VAR __Result = DIVIDE(__CumStop, __AllSelectedStopsCount) RETURN IF(__CurrentCount > 0, __Result, BLANK()) ) maybe somebody have some good ideas 🙂
Solved
cousinitt13
1 year ago Place DAX Commands and Tips
1.7KViews
0likes
6Comments
Chart showing incorrect month
hi guys , I have a data like below Project Proj Code Creation Date Won Date Lost Date Creation FY Creation FY Month No. Creation Month Won Fy Won Month Won FY Month No. Status Proj1 0001 26 March 2019 18 April 2019 2018-2019 12 March 2019-2020 April 1 Won Proj2 0002 24 September 2019 1 october 2019 2019-2020 6 September 2019-2020 October 7 Won Now to i have to show total projects in different statuses over current fiscal year's months.So below are the measures i have used This year won projects= Var MaxFYYear=MAX('Pipeline Master'[Won FY]) //To give me current fiscal year. Return CALCULATE(DISTINCTCOUNT('Pipeline Master'[Pipeline Code]),FILTER('Pipeline Master','Pipeline Master'[Won FY]=MaxFYYear),'Pipeline Master'[Status (groups)]="Won") Now when i am combining the above measure with "Won Month" column in a chart it shows "March" month too which is not even present in the data.Can you guys please help me why is it happening to me ?
Solved
Anonymous
2 years ago Place DAX Commands and Tips
1.2KViews
0likes
1Comment
Sum Filter the value in visual chart
Hi, I have 02 column chart to see the total of per date,day or per hour. how to get the sum value by filtering because the measure I make shows different value. thank you in advance.. My table: Date Day Time Sample Total 1-Aug Tue 12:00:00 AM Item 1 325 2-Aug Wed 12:00:00 AM Item 1 315 3-Aug Thu 12:00:00 AM Item 2 340 4-Aug Thu 3:00:00 AM Item 2 285 Measure Item 1 = CALCULATE( SUM('Equipment Query'[Total]), 'Equipment Query'[Sample] IN {"Item 1"})
jayceeb
3 years ago Place DAX Commands and Tips
665Views
0likes
4Comments
Measure in value field to ignore the legend of chart
Hi everybody, I have a problem with writing dax measure, that ignores legend field, so the data series is not repeated for every item by legend. I want my chart to show the rating (that is a simple average) and then to show category limits for ratings (ok, not ok, great) which are stored in separate table. I have two data types, so the ratings limits are diferent for each data type, but that is just a small problem. If i use this chart, it looks fine, beceause it has secondary axis: But in visuals with only one axis, the ratings limits measures start to interact with the legend, and creates the ratings limits line (area) for every single value by the legend. For the ratings limits I am using this measures: Not ok = LOOKUPVALUE(rating_limits[upper limit],rating_limits[Category],"Not Ok",rating_limits[Source ID],SELECTEDVALUE(Src_list[Source ID])) This one creates the dulicates of the data series. Then I've tried another measure with filters and ALL() but that one measure didn't work too. CALCULATE( AVERAGE(rating_limits[upper limit), FILTER(rating_limits, reating_limits[Category]="Not ok" && Rating_limits[Source ID]=SELECTEDVALUE(Src_list[Source ID]) ), ALLEXCEPT(Data,Data[Source ID]), ) The relations are just like this: Link for the example is here: https://drive.google.com/file/d/1zuRRJ42O_C4TSo6a73YDGOxhZGqfyhMK/view?usp=sharing Please help, I would be pleased 🙂 Thanks
Anonymous
3 years ago Place DAX Commands and Tips
1.6KViews
0likes
2Comments
Chart with last 4 weeks' data and secondary chart with just last week's data
Hello! This is my first forum post so please be understanding if I post too much info, I want to be 'easy' to help 🙂 I am trying to show 4 weeks of data in a chart, which is easy, but on the same power bi page in a different chart, show the most recent week worth of data too. I have the data automatically uploading from a database source. The data I have currently looks like this in my query, with the current chart showing the equipment name on x axis, and the OEE percentages on Y. There is a slider that is linked to Shift ISO column so that the last 4 weeks of data can be displayed. Is there any way to make a chart that only shows the last week's worth of data based on the time of accessing the power bi? thank you!
Solved
Anonymous
3 years ago Place DAX Commands and Tips
541Views
0likes
1Comment
How to filter the table by chart visual?
Hey there! So what we have here are: 1) "Status" chart 2) "Readiness" column in the table Both status chart sections and flag icons in the column are measures. A specific table is created for the chart, without any relationship to the other tables. What I want to: Whenever a user clicks on a section (say red, for example), the column in the table should display only the projects with red flag icons: For now, whenever I click on whatever section of the chart - the column doesn't get filtered. Like here: RESOURCES IF NEEDED Chart table: 1) table itself 2) color conditions Color = SWITCH('Status Chart Filter Table'[Flag], "FlagHigh", "At Plan", "FlagMedium", "Behind Plan", "FlagLow","At Risk", "FlagBlack", "Other" ) 3) table expression Status Chart Filter Table = VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 1, 4, 1 ), "Sort", [Value] ) VAR T2 = ADDCOLUMNS ( T1, "Flag", SWITCH ( [Sort], 1, "FlagHigh", 2, "FlagMedium", 3, "FlagLow", 4, "FlagBlack" ) ) RETURN T2 4) count the number of a particular color: Flag Count = VAR CurrentFlag =SELECTEDVALUE('Status Chart Filter Table'[Flag] ) VAR T1 = ADDCOLUMNS ( VALUES ( 'All Project v2'[Project Name] ), "@Flag", [stat chart] ) VAR T2 = FILTER ( T1, [@Flag] = CurrentFlag ) RETURN COUNTROWS ( T2 ) Conditional formatting expression used to mark "Readiness columns" with respective flags (if needed): _Overall_Status_Flag = VAR Unit_Readiness = [Upcoming Unit Readiness] #showing values in percentages VAR count_delimiter = LEN ( Unit_Readiness ) - LEN ( SUBSTITUTE ( Unit_Readiness, ",", "" ) ) #in the case when there are two values delimited with ",". Because conditional formatting #works on numbers only, we have to split the string (e.g. 100%, 99%) and check if they meet #conditions and assign a flag to them VAR last_Unit_Readiness_position = IF ( count_delimiter = 0, BLANK (), FIND ( "@", SUBSTITUTE ( Unit_Readiness, ",", "@", count_delimiter ) ) ) VAR last_Unit_Readiness_len = IF ( count_delimiter = 0, BLANK (), LEN ( Unit_Readiness ) - last_Unit_Readiness_position ) VAR last_Unit_Readiness = VALUE ( SWITCH ( TRUE (), ISERROR ( SEARCH ( "%", Unit_Readiness ) ), BLANK (), count_delimiter = 0, LEFT ( Unit_Readiness, LEN ( Unit_Readiness ) - 1 ), LEFT ( RIGHT ( Unit_Readiness, last_Unit_Readiness_len ), last_Unit_Readiness_len - 1 ) ) ) / 100 VAR upcoming_gates = [Upcoming Gate] # the [Unit Readiness] shows the readiness of the #particular gate VAR count_delimiter_gate = LEN ( upcoming_gates ) - LEN ( SUBSTITUTE ( upcoming_gates, ",", "" ) ) VAR last_gate_position = IF ( count_delimiter_gate = 0, BLANK (), FIND ( "@", SUBSTITUTE ( upcoming_gates, ",", "@", count_delimiter_gate ) ) ) VAR last_gate_len = IF ( count_delimiter_gate = 0, BLANK (), LEN ( upcoming_gates ) - last_gate_position ) VAR last_gate = SWITCH ( TRUE (), count_delimiter_gate = 0, upcoming_gates, LEFT ( RIGHT ( upcoming_gates, last_gate_len ), last_gate_len ) ) VAR min_p_calc = CALCULATE ( SELECTEDVALUE ( 'Unit Readiness Table'[Y_min] ), #[Y_min] is a min. the threshold value for readiness not to be red 'Unit Readiness Table'[P Gate] = TRIM ( last_gate ) # [P Gate] is a gate's name ) VAR min_m_calc = CALCULATE ( SELECTEDVALUE ( 'Unit Readiness Table M'[Y_min] ), 'Unit Readiness Table M'[M Gate] = TRIM ( last_gate ) ) VAR y_min_P = IF ( SELECTEDVALUE ( 'All Project v2'[Project Type] ) = "PMP", min_p_calc, min_m_calc ) VAR max_p_calc = CALCULATE ( SELECTEDVALUE ( 'Unit Readiness Table'[Y_max] ),# Y_max is the threshold to be green color 'Unit Readiness Table'[P Gate] = TRIM ( last_gate ) ) VAR max_m_calc = CALCULATE ( SELECTEDVALUE ( 'Unit Readiness Table M'[Y_max] ), 'Unit Readiness Table M'[M Gate] = TRIM ( last_gate ) ) VAR y_max_P = IF ( SELECTEDVALUE ( 'All Project v2'[Project Type] ) = "P", max_p_calc, max_m_calc ) VAR overall_status = SWITCH ( TRUE (), OR ( TRIM ( last_gate ) IN { "Start In", "Start De", "Start Proto", "Insufficient Data", "No Next Gate" }, ISBLANK ( last_Unit_Readiness ) ), BLANK (), last_Unit_Readiness < y_min_P, "FlagLow", last_Unit_Readiness > y_max_P, "FlagHigh", "FlagMedium" ) RETURN overall_status
Solved
Anonymous
3 years ago Place DAX Commands and Tips
1.1KViews
0likes
5Comments
Count Rows Within Table Chart
Hello everyone, Is there a way to count rows within the table chart ? I have a date column coming from calender table and another column (margin) from a Table (Table B) and the rest are from another table (Table A). I want to count rows where margin is >=1000. Table relationship is : Calender -> Table A -> Table B (single and one to many) Please help I want to get 8 as final answer
Solved
Farhan_Raza
3 years ago Place DAX Commands and Tips
896Views
0likes
3Comments
Sort graph axis values (DAX generated)
Hello community, I have created a Boxplot chart showing salary distribution per category. The categories are retrieved from a table that was created by DAX. As you can see in the photo, the categories order is random and I want to sort the categories so I have L3, L5, L6. How can I do that? Thanks in advance 🙂
Solved
Anonymous
3 years ago Place DAX Commands and Tips
581Views
0likes
2Comments
DAX measure - chart with current time
Hi, I have to present column chart that shows how many deliveries was done hourly. The data that I am using will be refreshed evey hour. I would like to show on the chart the period of 24 hours, distributed hourly, on the most recent date. What I manage to do (with my begginer skills) is to create a measure that show the max of date. The issue is that If I refresh the data around 4PM it takes the value for the next hours from the day before and I would like to leave it empty. Do you have any ideas how to resolve that? My intuiton tells me that I should use a refference to current time but have no idea what to do with that.
Solved
NewInThis
4 years ago Place DAX Commands and Tips
721Views
0likes
3Comments
Compare same measure in a chart with two sets of slicers (same slicers)
Hi all! I'm struggling with the following. I attached an image for better understanding I need to create a chart that plots a different measure based on selection. I know how to do that. The problem comes into play when I want to chart this measure comparing two same sets of slicers (I have 6 dimensions i need to use per each comparable). I have come to two possible solutions but they are not as clean as desired. One is overlapping two charts showng the same measure but adapting interactions between slicers and charts. The sencond one would be duplicating all dimensions tables/columns and duplicating all measures to use the ALLSELECTED function to adapt each measure to each set dimensions, but that would be messy. Any idea on how to make it? Thanks!
XTF
4 years ago Place DAX Commands and Tips
739Views
0likes
2Comments