Table Visualization doesn't work when Measure is added
I'm creating a visual that shows the Operator Name, his/her Operator Code, and the associated amount of scrap ($) associated with their work. I can get the Operator Name and Operator Code to match up, but when I add the measure, the measure shows the Total value all the way down. I recognize this as a relationship problem, but I have the Operator Code in both of the tables from which I'm pulling the data so I figured that would solve the relationship problem. The only other thing I can think of is that the measure somehow is messing up the relationship. ProdData Item_No Operator Vertical Top Bottom Model Vertical Min Length Vertical Max Length F/B Min Length F/B Max Length T/B Min Length T/B Max Length Vertical IT/OT Top IT/OT Bottom IT/OT Complete I-beam Count Incomplete I-beam Count 1 Op-1 199.9982 99.9979 60.0011 HP500 199.99 200.02 99.99 100.07 59.998 60.003 IT IT IT 1 0 21 Op-1 200.0143 99.9879 59.9993 HP500 199.99 200.02 99.99 100.07 59.998 60.003 IT OT IT 0 1 22 Op-1 200.0034 100.0046 60.0027 HP500 199.99 200.02 99.99 100.07 59.998 60.003 IT IT IT 1 0 23 Op-1 200.0161 100.0034 59.9988 HP500 199.99 200.02 99.99 100.07 59.998 60.003 IT IT IT 1 0 24 Op-1 199.9845 99.9937 59.9988 HP500 199.99 200.02 99.99 100.07 59.998 60.003 OT IT IT 0 1 Part Costs Model Vertical Cost Top Cost Bottom Cost I-Beam Cost HP500 $ 4.95 $ 2.87 $ 6.01 $ 13.83 Operators Operator Code Operator Name Model Op-1 Anderson HP500 Op-2 Betts HP500 Op-3 Carhart HP500 Op-4 Dennison HP500 Op-5 Evers HP500 Op-6 Franks HP500 Op-7 Guilfoyle HP500 Op-8 Harris HP500 Op-9 Ingalls HP500 Op-10 Janison HP500 Op-11 Kelty HP500 Op-12 Lassiter HP500 Op-13 Michaels HP500 Op-14 Norris HP500 Op-15 Oates HP500 Op-16 Payton HP500 Op-17 Quincy HP500 Op-18 Rawlings HP500 Op-19 Stevens HP500 Op-20 Thomas HP500 Measure I-Beam Scrap Cost = SUMX ( VALUES ( ProdData[Model] ), VAR OperatorIncompleteCount = CALCULATE ( SUM ( 'ProdData'[Incomplete I-beam Count] ), 'ProdData'[Incomplete I-beam Count] = 1, 'ProdData'[Model] = EARLIER ( ProdData[Model] ) ) RETURN OperatorIncompleteCount * SUMX ( RELATEDTABLE('Part Costs'), 'Part Costs'[I-Beam Cost] ) )Create a Flag measure based on Start date and End Date.
Hello All, I have a table and I need to show records between start date and End date. Need to create a flag which has to be 1 for dates which is between start date and End Date. So that I can drop flag in measure and select "1" to show the records. Below is the Dax I used but gettting error "Multiple Values supplied instead of one" Flag = Var startdate=SELECTEDVALUE('Selection Values'[CAL_DT]) var Startdate_plus = startdate+1 Var PStrt_date= startdate-365 var enddate= startdate+14 return IF(AND(VALUES(V_DAILY_SERVICE_CALL_LOG[CAL_DT]) >= IF(VALUES(V_DAILY_SERVICE_CALL_LOG[STATUS_ID]) IN {"A501","A405","A411"},PStrt_date,Startdate_plus),VALUES(V_DAILY_SERVICE_CALL_LOG[CAL_DT]) <= enddate),1,0 Here (V_DAILY_SERVICE_CALL_LOG[CAL_DT] is retireved by using lookup from date table. Below is the model: My fact table is V_DAILY_SERVICE_CALL_LOG. Date slicer is coming from a separate table which is not in relation with Fact table and Service dest is coming from a Dimension table which is in One to Many relation with Fact. Relative SQL query for flag is below, I want to convert that Flag query to Flag measure so that I can drop this to Table Vizualization filter pane:- Select * from( Select f.*, CASE When d.CAL_DT BETWEEN (CASE WHEN STATUS_ID IN ('A501','A405','A411') THEN DATEADD(DAY,-365,'2023-03-04') ELSE DATEADD(DAY,1,'2023-03-04') END) AND DATEADD(DAY,14,'2023-03-04') THEN 1 ELSE 0 END as Flag from "LESL_DW_UAT"."PBI_MART"."V_DAILY_SERVICE_CALL_LOG" f inner join "LESL_DW_UAT"."PBI_MART"."V_DATE_DIM" d on f.DT_SKEY = d.DT_SKEY inner join "LESL_DW_UAT"."PBI_MART"."V_STORE_SERVICE_DIM" s on f.STORE_KEY = s.STORE_KEY where s.SERVICE_DSTRCT_CD ='22' ) A where Flag = 1
Pulling field in another table connected via an inactive relationship
Hello I have the three table below: that are related as follows: (1)-> *: Active Directory User[User], Workspace_All_RAW[user] (1)-> *: Active Directory User[User], Datasets[ConfigureBy], Inactive (1)-> *: Dataset[DatasetID], Workspace_All_Raw[DatasetID] In my report, i am creating a slicer based on Active Directory User[User]. I am trying to pull the Name of workspace from Workspace_All_Raw table (working good) and the Dataset Name from the DataSets table using a measure: Measure = CALCULATE(SELECTEDVALUE(DataSets[Name]),USERELATIONSHIP(DataSets[ConfiguredBy],'Active Directory Users'[Users])) but my measure is showing nothing when placed in a table visual. 1- How can i get the datasets name? 2- Is it possible to redesign my model in a different way to avoid having this inactive relationship?
How to handle this requirement?
Hi all, I've been struggling with a scenario for a while which I want to solve properly. Below I've created a model which contains the relevant tables from a more complex data model. The requirement Calculate the following Divide this SUM(Fact_Marketvalue.Marketvalue) * SUM(Fact_Rating.Carbonemission) where the Fact_Ratings.Carbonemission > 5) by this SUM(Fact_Marketvalue.Marketvalue) > 5 ) The result should by sliceable by Category, Customer, Security and more Dimension tables in my model. Right now I created many-many relationships between Fact_MarketValue and Fact_Ratings on the Security ID, but I want to avoid this relationship. What would be the best thing to do? Create a view in SQL which holds all information needed, if so, I want to keep it as lean as possible? or should I create a bridge table, if so, with what information? Or maybe I am missing some DAX magic..
