requesting help with duplicating row counts by counting # of rows * number of columns

[cuseman03]

Hi,

so i hope the solution is relatively easy for my problem.  My measure is overcouting rows.  So let's say I have a table with 10 columns and 1000 rows.  Well rather than return the 1000 cout for total rows, it's returning 10,000 because it's taking 1000 and multiplying it by each column.  It's doing this because my sql connection has a field called #ofrecords and it enters the amount of records in that table.  However, the query has a column called fields so again, it's counting total rows * every column.

Example

TableName	ColumnName	NumberOfRecordsInTable
PurchaseOrders	Sup Itm ID	34,799,673
PurchaseOrders	Submitter Name	34,799,673
PurchaseOrders	Submitter	34,799,673
PurchaseOrders	Sched Num	34,799,673
PurchaseOrders	Sales Tax	34,799,673
PurchaseOrders	Receipt Status	34,799,673
PurchaseOrders	Receipt Qty	34,799,673
PurchaseOrders	Receipt Amount	34,799,673
PurchaseOrders	Rec dttm	34,799,673
PurchaseOrders	Project	34,799,673
PurchaseOrders	PO Vendor Name	34,799,673
PurchaseOrders	PO Vendor	34,799,673
PurchaseOrders	PO Status	34,799,673
PurchaseOrders	PO Qty	34,799,673
PurchaseOrders	PO Price	34,799,673
PurchaseOrders	PO No	34,799,673
PurchaseOrders	PO Dispatched Date	34,799,673
PurchaseOrders	PO Date	34,799,673
PurchaseOrders	PO BU	34,799,673
PurchaseOrders	PO Amount	34,799,673
PurchaseOrders	Line2	34,799,673
PurchaseOrders	Line	34,799,673
PurchaseOrders	Item	34,799,673
PurchaseOrders	Freight	34,799,673
PurchaseOrders	Expected Receiving Date	34,799,673
PurchaseOrders	Due	34,799,673
PurchaseOrders	Distribution Status	34,799,673
PurchaseOrders	CAP #	34,799,673
PurchaseOrders	BRN - Budget Reference No	34,799,673
PurchaseOrders	Asset Profile	34,799,673
PurchaseOrders	Address 3	34,799,673
PurchaseOrders	Address 2	34,799,673
PurchaseOrders	Account Type	34,799,673
PurchaseOrders	Account Descr	34,799,673
PurchaseOrders	Account	34,799,673
PurchaseOrders	St	34,799,673
PurchaseOrders	Ship To Name	34,799,673
PurchaseOrders	Ship To	34,799,673
PurchaseOrders	RunSourceID	34,799,673
PurchaseOrders	Requisition Status	34,799,673
PurchaseOrders	Requisition Name	34,799,673
PurchaseOrders	Requester Name	34,799,673
PurchaseOrders	Requester	34,799,673
PurchaseOrders	Req Vendor Name	34,799,673
PurchaseOrders	Req Vendor	34,799,673
PurchaseOrders	Req ID	34,799,673
PurchaseOrders	Req Date	34,799,673
PurchaseOrders	Req BU	34,799,673
PurchaseOrders	Req Amount	34,799,673
PurchaseOrders	Postal	34,799,673
PurchaseOrders	KP Order Type	34,799,673
PurchaseOrders	Item Description	34,799,673
PurchaseOrders	GL Loc	34,799,673
PurchaseOrders	FileDate	34,799,673
PurchaseOrders	Dept Name	34,799,673
PurchaseOrders	Dept	34,799,673
PurchaseOrders	Cntry	34,799,673
PurchaseOrders	City	34,799,673
PurchaseOrders	Category Description	34,799,673
PurchaseOrders	Category Code	34,799,673
PurchaseOrders	Address 1	34,799,673

what I need is the simply the '34,799,673' counted once.

Thanks,

[Anonymous]

HI @cuseman03 ,

In my opinion, I'd like to suggest you add more conditions to filter on the range of calculated column or try to use measure formula with filter conditions to instead column.

Calculated Columns and Measures in DAX 

Measure Totals, The Final Word 

In addition, can you please share more detail information about your scenario? I' m not so clear about your expression and calculation logic.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[parry2k]

@cuseman03 try this measure

Total Rows = 
SUMX( 
VALUES ( Table[TableName] ), 
CALCULATE ( SUM ( Table[NumberofRecordsintable ) )
)